Product analytics in SQL
These are the metrics behind every product dashboard, and favourite topics in analyst interviews. The examples use Streamly, a fictional music app with users, listening sessions and subscriptions.
Active users
Daily active users (DAU) = distinct users with activity on a day. Count distinct people, not events. The same pattern by week or month gives WAU and MAU.
SELECT played_on AS day, COUNT(DISTINCT user_id) AS dau FROM plays WHERE played_on >= '2026-04-01' GROUP BY played_on ORDER BY day;
Month-over-month growth
Aggregate per month, then compare with LAG. Growth = (this − previous) ÷ previous.
WITH m AS (
SELECT strftime('%Y-%m', played_on) AS month, COUNT(*) AS plays
FROM plays GROUP BY month
)
SELECT month, plays,
ROUND(100.0 * (plays - LAG(plays) OVER (ORDER BY month)) / LAG(plays) OVER (ORDER BY month), 1) AS growth_pct
FROM m ORDER BY month;Funnels and conversion
Start from the top of the funnel and LEFT JOIN later steps, so people who dropped out still count. Conversion = reached the step ÷ entered the funnel.
SELECT u.source, COUNT(*) AS users, COUNT(DISTINCT p.user_id) AS listened, COUNT(DISTINCT s.user_id) AS subscribed FROM users u LEFT JOIN plays p ON p.user_id = u.id LEFT JOIN subscriptions s ON s.user_id = u.id GROUP BY u.source;
Cohort retention
Group users by when they started (the cohort), measure time since start, and count who's still active in each period.
SELECT strftime('%Y-%m', u.signed_up_on) AS cohort,
CAST((julianday(p.played_on) - julianday(u.signed_up_on)) / 7 AS INTEGER) AS week,
COUNT(DISTINCT u.id) AS active_users
FROM users u JOIN plays p ON p.user_id = u.id
WHERE CAST((julianday(p.played_on) - julianday(u.signed_up_on)) / 7 AS INTEGER) <= 3
GROUP BY cohort, week
ORDER BY cohort, week;Common mistakes
COUNT(*) where COUNT(DISTINCT user_id) is meant: sessions instead of people.
Inner joins in funnels, which silently drop everyone who didn't convert.
Comparing incomplete periods (this month so far vs last full month).
Questions
What's a good DAU/MAU ratio?+
It measures stickiness: the share of monthly users active on a typical day. Around 20% is common for consumer apps, and above 50% is exceptional.
Should analytics run on the production database?+
For small apps, read-only queries are fine. As you grow, copy data to a warehouse so heavy reports can't slow the product down.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.