PoPostgres: get most recent corresponding date for series of months - 2, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table location_ratings as select * from ( values
    (1, 1, '2017-4-7'::date, 1),
    (2, 1, '2017-7-3'::date, 2),
    (3, 1, '2017-9-9'::date, 5),
    (4, 1, '2017-11-2'::date, 4),
    (5, 2, '2017-1-3'::date, 3),
    (9, 2, '2017-3-7'::date, 1),
    (12, 3, '2017-2-7'::date, 2),
    (13, 3, '2017-3-4'::date, 4),
    (15, 3, '2017-10-1'::date, 1)
) as t(id, location_id, date, rating);

with months as (
    select generate_series(date_trunc('month', min("date")), 
                           date_trunc('month', max("date")+'1 month'::interval),
                           '1 month') as mon
    from location_ratings
),
month_rankings as (
    select distinct on (l.location_id, m.mon)
        m.mon, l.location_id, l.rating, l.date as rating_date 
    from months m
    left join location_ratings l on m.mon >= l.date
    where l.location_id is not null
    order by l.location_id, m.mon, l.date desc
)
--select * from month_rankings;
select mon, rating, count(*)
from month_rankings
group by 1,2 order by 1 asc,2 asc


absolute service time: 0,39 sec

PoPostgres: get most recent corresponding date for series of months - ...