How to build one SQL query to select the number of unique users for each day of the period?

Question

PostgreSQL

Hello.
Is it possible to get the data I need with a single query (without a stored function) in PostgreSQL?
There is a table of visits in which there is a column time and user ID.
let's say it has the following data (if grouped)
day1: 10 unique users
day2: 8 unique users (but 3 of them logged in on the first day)
day3: 15 unique users (but 6 of them were on the first or second day)
I need get this result:
row1: 10
row2: 15 (number of unique users who logged in on days 1-2)
row3: 24 (number of unique users who logged in on days 1-3)
Is this possible with a single query? Thanks

Answer 1

yeah
, one date list, the second table of visits, left join by the condition that the visit was from the beginning of the period to the end of the day of the date from the first list, group by, count(distinct user_id)