I would like to write a query that counts how many rows my transaction table contains on the last 3 days, grouping by date with each days counting all x days back.
table t:
| Arrival_date | * |
|---|---|
| 2022-06-15 00:01:02 | ... |
| 2022-06-15 13:02:54 | ... |
| 2022-06-16 02:34:13 | ... |
| 2022-06-17 10:23:00 | ... |
| 2022-06-17 11:42:00 | ... |
| 2022-06-18 23:00:00 | ... |
| 2022-06-19 01:00:13 | ... |
| 2022-06-19 20:21:22 | ... |
| 2022-06-20 09:23:13 | ... |
| 2022-06-20 18:00:00 | ... |
Final table:
| Arrival_date | nb3days | nb1day |
|---|---|---|
| 2022-06-16 | 3 | 3 |
| 2022-06-17 | 5 | 3 |
| 2022-06-18 | 4 | 3 |
| 2022-06-19 | 5 | 3 |
| 2022-06-20 | 6 | 4 |
schema: https://i.imgur.com/WPiHnk5.png
here's what I tried to do :
I have created a calendar table named dates and ran:
select dates.date,
count (*) filter (where date(t.arrival_date) > dates.date - interval '3
day') nb3days,
count (*) filter (where date(t.arrival_date) > dates.date - interval '1
day') nb1day
from t, dates
where date(t.arrival_date) = dates.date
group by dates.date
I am struggling to understand why isn't my query working and what I need to change for it to work, i'm not even sure if I'm tackling the problem correctly.