[SQL] How to calculate DAU, MAU based on daily clicks with SQL

DAU - daily active users, the number of users who use the product one or more times in a day (it doesn't matter if the user spent the whole day or made one click in the app - we count it as one user)

MAU - Monthly Active Users, the number of users who use the product one or more times per month.

SF - Sticky Factor. A metric calculated daily as DAU/MAU * 100%. Here we use MAUs for the previous 30 days, so the MAU boundaries shift with each date.

We have a table clicks with date, product and user.



And expected result is:



DAU is easily calculated by COUNT(DISTINCT(user)). I've tried to find MAU using Window fuction and CTE (as in code snippet below), but with no success.

WITH RECURSIVE t(date) AS (
SELECT
CURRENT_DATE as date
,c1.product AS cmd
,COUNT(DISTINCT(c1.user)) as mau
FROM clicks c1
WHERE (c1.date BETWEEN CURRENT_DATE - interval '30 day' AND CURRENT_DATE)
GROUP BY 1, 2
UNION ALL
SELECT
(t.date - interval '1 day'):ATE date,
,c2.product
,COUNT(DISTINCT(c2.user)) as mau
FROM t, clicks c2
WHERE (c2.date BETWEEN t.date - interval '30 day' AND t.date)
GROUP BY 1, 2)
SELECT *
FROM t;


How to build a query to this table to calculate DAU, MAU and SF for each product and day?

Continue reading...