sqlstreak

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.

Practise it now