194. Daily active users report for new market

easy snap

*******

*********************************************************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
14%
196 User segments report snap hard
18%
197 Students who didn't finish all subjects snap easy
25%
198 Average score per subject snap easy
50%
199 Students improvement snap hard
67%
200 Last week's absent students snap medium
67%
201 Top 3 students for each subject snap hard
67%