*******
*********************************************************2022-03-01********************************************
***********
- *********************************2022-03-01 ***2022-03-31
- ******************************************
- ***************************************************
Table 1: dates
Calendar dates from 01/01/2019 to 12/31/2025.
col_name | col_type ----------+---------- year | smallint month | smallint date | date
Table 2: snap_session
col_name | col_type ----------------+-------------------------- user_id | bigint event | text dt | date -- date of the event
Sample results
date | count
------------+-------
2022-03-01 | 9
2022-03-02 | 7
2022-03-03 | 0
2022-03-04 | 6
2022-03-05 | 15
Solution postgres
SELECT D.date, count(DISTINCT S.user_id) dau
FROM dates D
LEFT JOIN snap_session S
ON D.date = S.dt
WHERE D.date >= '2022-03-01'
AND D.date <= '2022-03-31'
GROUP BY 1
ORDER BY 1;
Explanation
This query is selecting data from two tables, "dates" and "snap_session". It is counting the number of unique user IDs that occurred on each day between March 1, 2022, and March 31, 2022.
The "LEFT JOIN" statement ensures that all dates from the "dates" table are included in the final result, even if there are no matching records in the "snap_session" table.
The "WHERE" clause is used to filter the results to only include dates within the specified time frame.
The "GROUP BY" clause groups the results by date.
Finally, the "ORDER BY" clause orders the results by date in ascending order.
Expected results
More Snap questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 195 | MoM user growth snap | hard |
|
| 196 | User segments report snap | hard |
|
| 197 | Students who didn't finish all subjects snap | easy |
|
| 198 | Average score per subject snap | easy |
|
| 199 | Students improvement snap | hard |
|
| 200 | Last week's absent students snap | medium |
|
| 201 | Top 3 students for each subject snap | hard |
|