Retrieve Records of the Last Consecutive Dates in PostgreSQL

Edit Sorry for being unclear in the first post. If the item appears only once, it should also be included. Please, check updated sample table…

I have a table of record of sales of each item per day. A candidate key for this table would be the combination of item_id and date. What I’m trying to get is a view of all records from the last (latest) consecutive dates of each item. If the item only appear once, it should also be included.

What I have

+---------+------------+------------+
| item_id |    date    | gross_sale |
|  (text) |   (date)   |  (double)  |
+---------+------------+------------+
| 11aa    | 2019-01-01 |         45 |
| 11aa    | 2019-01-02 |         85 |
| 22bb    | 2019-01-01 |         65 |
| 22bb    | 2019-01-02 |         15 |
| 22bb    | 2019-01-03 |         45 |
| 11aa    | 2019-01-05 |         32 |
| 33cc    | 2019-01-05 |         11 |
| 22bb    | 2019-01-05 |         23 |
| 22bb    | 2019-01-06 |         56 |
| 11aa    | 2019-01-06 |         78 |
| 11aa    | 2019-01-07 |         12 |
| 11aa    | 2019-01-08 |         45 |
+---------+------------+------------+

​x
 
+---------+------------+------------+| item_id |    date    | gross_sale ||  (text) |   (date)   |  (double)  |+---------+------------+------------+| 11aa    | 2019-01-01 |         45 || 11aa    | 2019-01-02 |         85 || 22bb    | 2019-01-01 |         65 || 22bb    | 2019-01-02 |         15 || 22bb    | 2019-01-03 |         45 || 11aa    | 2019-01-05 |         32 || 33cc    | 2019-01-05 |         11 || 22bb    | 2019-01-05 |         23 || 22bb    | 2019-01-06 |         56 || 11aa    | 2019-01-06 |         78 || 11aa    | 2019-01-07 |         12 || 11aa    | 2019-01-08 |         45 |+---------+------------+------------+​

What I want

+---------+------------+------------+
| item_id |    date    | gross_sale |
+---------+------------+------------+
| 11aa    | 2019-01-05 |         32 |
| 11aa    | 2019-01-06 |         78 |
| 11aa    | 2019-01-07 |         12 |
| 11aa    | 2019-01-08 |         45 |
| 22bb    | 2019-01-05 |         23 |
| 22bb    | 2019-01-06 |         56 |
| 33cc    | 2019-01-05 |         11 |
+---------+------------+------------+

 
+---------+------------+------------+| item_id |    date    | gross_sale |+---------+------------+------------+| 11aa    | 2019-01-05 |         32 || 11aa    | 2019-01-06 |         78 || 11aa    | 2019-01-07 |         12 || 11aa    | 2019-01-08 |         45 || 22bb    | 2019-01-05 |         23 || 22bb    | 2019-01-06 |         56 || 33cc    | 2019-01-05 |         11 |+---------+------------+------------+​

I read from several post that it can simply be done using CTE and window but I’m still quite new to them. If you don’t mind, could you please include a small explanation on how your code works too?

Cheers!

Answer

You can enumerate the rows and add an increasing sequence starting from the end of the data

select t.*
from (select t.*,
             row_number() over (partition by item_id order by date desc) as seqnum,
             max(date) over (partition by item_id) as max_date
      from t
     ) t
where date + (seqnum - 1) * interval '1 day' = max_date

 
select t.*from (select t.*,             row_number() over (partition by item_id order by date desc) as seqnum,             max(date) over (partition by item_id) as max_date      from t     ) twhere date + (seqnum - 1) * interval '1 day' = max_date​

Advertisement

Answer