Q1: DAU/MAU stickiness ratio

The question: "Calculate the DAU/MAU stickiness ratio for each day in the last month."

dau-mau-stickiness.sql
WITH daily_active AS (
  SELECT activity_date, COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY activity_date
),
monthly_active AS (
  SELECT d.activity_date,
    COUNT(DISTINCT e.user_id) AS mau
  FROM daily_active d
  JOIN events e
    ON e.activity_date BETWEEN d.activity_date - INTERVAL '29 days' AND d.activity_date
  GROUP BY d.activity_date
)
SELECT da.activity_date, da.dau, ma.mau,
  ROUND(da.dau * 100.0 / ma.mau, 1) AS stickiness_pct
FROM daily_active da
JOIN monthly_active ma ON ma.activity_date = da.activity_date
ORDER BY da.activity_date;

What to say out loud: a rolling 30-day window, not a fixed calendar month, is usually the right definition of MAU for a daily stickiness metric — a fixed-month version would make stickiness swing artificially at the start of every new month.

Q2: Feature adoption rate

The question: "A new filter feature launched. What percentage of users who had access to it have actually used it?"

feature-adoption.sql
SELECT
  ROUND(
    COUNT(DISTINCT CASE WHEN e.event_name = 'used_filter' THEN u.user_id END) * 100.0
    / COUNT(DISTINCT u.user_id),
  1) AS adoption_rate_pct
FROM users u
LEFT JOIN events e ON e.user_id = u.user_id
WHERE u.has_filter_access = TRUE;

The trap: computing this against the entire user base instead of just the population with actual access to the feature (a staged rollout, a paywall, a platform restriction). The WHERE u.has_filter_access = TRUE filter is what makes the denominator correct — without it, the rate is understated by counting users who could never have used the feature in the first place.

Q3: Compare A/B test conversion rates

The question: "Compare the conversion rate between the control and treatment groups of an A/B test."

ab-test-conversion.sql
SELECT
  variant,
  COUNT(DISTINCT user_id) AS users_exposed,
  COUNT(DISTINCT CASE WHEN converted THEN user_id END) AS conversions,
  ROUND(
    COUNT(DISTINCT CASE WHEN converted THEN user_id END) * 100.0
    / COUNT(DISTINCT user_id),
  2) AS conversion_rate_pct
FROM ab_test_exposures
GROUP BY variant;
Say this out loud, don't just run the query: a 2 percentage-point difference between variants isn't automatically meaningful — whether it's statistically significant depends on sample size and requires a proper test (chi-squared or a z-test for proportions), not just eyeballing the two percentages. SQL computes the inputs to that test; it doesn't run the test itself.

Q4: Build a retention curve

The question: "Build a retention curve showing what percentage of each signup cohort is still active at day 1, day 7, and day 30."

retention-curve.sql
WITH cohorts AS (
  SELECT user_id, signup_date FROM users
),
activity AS (
  SELECT c.user_id, c.signup_date,
    e.activity_date - c.signup_date AS day_offset
  FROM cohorts c
  JOIN events e ON e.user_id = c.user_id
)
SELECT
  signup_date,
  COUNT(DISTINCT CASE WHEN day_offset = 1  THEN user_id END) * 100.0 / COUNT(DISTINCT user_id) AS day1_pct,
  COUNT(DISTINCT CASE WHEN day_offset = 7  THEN user_id END) * 100.0 / COUNT(DISTINCT user_id) AS day7_pct,
  COUNT(DISTINCT CASE WHEN day_offset = 30 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id) AS day30_pct
FROM activity
GROUP BY signup_date;

This is the same cohort/period-offset pattern as full retention cohort analysis, condensed to specific milestone days instead of every period — worth recognizing as the same underlying technique rather than a new one.

Q5: Users who dropped off after a specific feature

The question: "Find users who used a specific feature once and then never returned to the product at all."

dropoff-after-feature.sql
WITH feature_users AS (
  SELECT user_id, MAX(event_at) AS last_feature_use
  FROM events
  WHERE event_name = 'used_new_export_tool'
  GROUP BY user_id
),
last_activity AS (
  SELECT user_id, MAX(event_at) AS last_seen
  FROM events
  GROUP BY user_id
)
SELECT f.user_id, f.last_feature_use, l.last_seen
FROM feature_users f
JOIN last_activity l ON l.user_id = f.user_id
WHERE f.last_feature_use = l.last_seen;

When a user's last-ever event is that feature event, it's a strong (though not certain) signal the feature caused them to leave — a valuable, if correlational, list for the product team to investigate qualitatively.

Q6: Engagement score trend over time

The question: "The team uses a composite engagement score (a weighted combination of logins, actions taken, and content created). Show how the average score has trended month over month."

engagement-score-trend.sql
WITH monthly_score AS (
  SELECT
    DATE_TRUNC('month', activity_date) AS month,
    user_id,
    (logins * 1) + (actions_taken * 2) + (content_created * 5) AS engagement_score
  FROM user_monthly_activity
)
SELECT month, ROUND(AVG(engagement_score), 1) AS avg_engagement_score
FROM monthly_score
GROUP BY month
ORDER BY month;

Worth raising unprompted: the weights (1, 2, 5) in a composite score are a product decision, not a technical one — a strong candidate asks who defined them and whether they're still the right proxy for "engaged," rather than treating the formula as fixed ground truth.

Key takeaways

  • DAU/MAU stickiness typically uses a rolling 30-day MAU window, not a fixed calendar month.
  • Feature adoption rate must be scoped to users who actually had access to the feature, not the whole user base.
  • SQL computes A/B test inputs; statistical significance requires an actual test beyond a raw percentage comparison.
  • Retention curves and milestone-day retention are the same cohort/period-offset pattern, just aggregated differently.
  • Composite engagement scores encode product judgment in their weights — worth questioning, not just querying.