How to get last value with condition in postgreSQL?

Question

I have a table in postgres with three columns, one with a group, one with a date and the last with a value.

grp	mydate	value
A	2021-01-27	5
A	2021-01-23	10
A	2021-01-15	15
B	2021-01-26	7
B	2021-01-24	12
B	2021-01-15	17

question from:https://stackoverflow.com/questions/65947623/how-to-get-last-value-with-condition-in-postgresql

Answer 1

step-by-step demo:db<>fiddle

SELECT
    grp,
    gs::date as mydate,
    value
FROM (
    SELECT
        *,
        COALESCE(                                                             -- 2
             lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1,        -- 1 
             mydate
        ) as prev_date
    FROM foo
) s,
    generate_series(mydate, prev_date, interval '-1 day') as gs               -- 3
ORDER BY grp, mydate DESC                                                     -- 4

1. lead() window function shifts the next value of an ordered group (= partition) into the current one. The group is already defined, the order is the date. This can be used to create the required date range. Since you don't want to have the last date twice (as end of the first range and beginning of the next one) the end date stops - 1 (one day before the next group starts)
2. This is for the very last records of the groups: They don't have a following record, so lead() yield NULL. To avoid this, COALESCE() sets them to the current record.
3. Now, you can create a date range with the current and the next date value using generate_series().
4. Finally you can generate the required order