- ********************************#194
- *******************************************************************************************************************************************
- **************************************************************************************
- ***********************************************************************************
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
Solution postgres
WITH mau AS (
SELECT D.year, D.month, count(DISTINCT S.user_id) active_users
FROM dates D
LEFT JOIN snap_session S
ON D.date = S.dt
WHERE D.date >= '2021-03-01'
AND D.date <= '2022-04-30'
GROUP BY 1, 2
), mau_growth AS (
SELECT year, month, (active_users - LAG(active_users, 1) OVER (ORDER BY year, month)) * 100.0/ LAG(active_users, 1) OVER (ORDER BY year, month) AS mom_growth
FROM mau
)
SELECT year, month, mom_growth
FROM mau_growth
WHERE mom_growth IS NOT NULL;
Explanation
This query is calculating the monthly active user (MAU) growth rate for a specific date range.
The query first creates a Common Table Expression (CTE) called "mau" which counts the number of distinct active users for each month between March 2021 and April 2022. It does this by joining a table called "snap_session" with a table called "dates", using the date column as the join key. It then groups the results by year and month.
The second CTE called "mau_growth" uses the previous CTE "mau" to calculate the month-over-month (MoM) growth rate of active users. It does this by subtracting the previous month's active user count from the current month's count, dividing that difference by the previous month's count, and multiplying by 100 to get a percentage growth rate. It uses the LAG function to get the previous month's count for the division.
Finally, the main query selects the year, month, and MoM growth rate from the "mau_growth" CTE for all months with a non-null growth rate. This result set can be used to track the growth rate of active users over time and identify any trends or changes in user behavior.
Expected results