I have a table with the following schema:
id itemid date some additional data
1 1000 10/12/2020 a
2 1000 10/12/2020 b
3 1002 09/12/2020 c
4 1001 07/12/2020 d
5 1000 05/12/2020 e
6 1005 03/12/2020 f
7 1003 03/12/2020 g
In this table only the id field is unique. I'm concerned with getting the rows containing the last X distinct itemid, ordered by date.
For example, in the sample above, if I'd like to get the last 3 distinct itemid, I'd be getting the first 4 rows, since in the first 4 rows we have three distinct itemid: 1000, 1002 and 1001. I'm not sure how to achieve this using a single SQL statement.
If I understand correctly, you would like to count the number of distinct item ids up to each each row (by date) and return all rows where the count is three.
If Postgres supported this, you could use:
select t.*
from (select t.*,
count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
from (select t.*,
min(id) over (partition by itemid order by date desc) as min_id
from t
) t
) t
where cnt_itemid <= 3;
Alas, Postgres does not support COUNT(DISTINCT) as a window function. But you can calculate it using DENSE_RANK():
select t.*
from (select t.*,
count(*) over (filter where id = min_id) as cnt_itemid
from (select t.*,
min(id) over (partition by itemid order by date) as min_id
from t
) t
) t
where cnt_itemid <= 3;
However, this returns all the most recent rows up before the 4th item -- so it has extra rows.
To get four rows, you want the first where the item id is "3". One method is:
select t.*
from (select t.*, min(id) filter (where cnt_itemid = 3) over () as min_cnt_itemid_3
from (select t.*,
count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
from (select t.*,
min(id) over (partition by itemid order by date desc) as min_id
from t
) t
) t
) t
where id <= min_cnt_itemid_3;
You can also do this by identifying the first occurrence of the "third item" and then choosing all rows up to that row:
select t.*
from t join
(select itemid, min(max_date) over () as min_max_date
from (select t.itemid, max(date) as max_date
from t
group by t.itemid
order by max(t.date) desc
limit 3
) t
) tt
on t.itemid = tt.itemid and t.date >= tt.min_max_date;
This fiddle shows each of these.