MAU (Monthly Active Users) is the number of active users who use a service at least once per month, making it a key KPI for measuring product scale and retention. The LAG() window function is useful when analyzing monthly MAU trends. Because it can retrieve the value N rows before the current row, it calculates growth rates such as month-over-month growth without subqueries or self-joins.

LAG(mau) OVER (ORDER BY month)
-- retrieve the mau from the “previous row” in month order
-- return NULL for the first row because there is no previous row

(mau - prev_mau) * 100.0 / NULLIF(prev_mau, 0)
-- NULLIF(prev_mau, 0): return NULL when prev_mau=0 to prevent division by zero
-- * 100.0: convert to floating-point arithmetic and avoid integer division

From the monthly_kpi table, calculate each month's MAU, previous-month MAU (prev_mau), and month-over-month growth rate (mau_growth_pct). Return month, mau, prev_mau, mau_growth_pct in ascending month order. Round the growth rate to two decimal places.

Expected output (month ascending):

WITH lagged AS (
  SELECT
    month, mau,
    LAG(mau) OVER (ORDER BY month) AS prev_mau  -- retrieve the previous month's MAU
  FROM  monthly_kpi
)
SELECT
  month, mau, prev_mau,
  ROUND(
    (mau - prev_mau) * 100.0
    / NULLIF(prev_mau, 0), 2  -- avoid division by 0 (NULL when 0)
  ) AS mau_growth_pct         -- month-over-month growth rate (%)
FROM  lagged
ORDER BY month;

/*
  Execution order:
  1. FROM monthly_kpi                         → read the rows
  2. LAG(mau) OVER (ORDER BY month)           → attach the previous month's mau
  3. WITH lagged                              → complete the CTE
  4. (mau-prev_mau)*100.0/NULLIF(prev_mau,0)  → calculate the growth rate
  5. ROUND(..., 2) / ORDER BY month           → round and output in month order
  */

Conversion rate (CVR) measures the share of users who take a target action, such as purchasing or becoming a paying customer, among users who took an earlier action such as visiting or signing up. Funnel analysis breaks this down by step to quantify where users drop off in the path from “signup → trial → purchase.” CVR = users passing a step / users passing the previous step; calculating it this way identifies the bottleneck that needs improvement.

COUNT(DISTINCT CASE WHEN step = 'signup' THEN user_id END)
-- return user_id only for rows where step='signup'; NULL otherwise
-- COUNT(DISTINCT) ignores NULL, so only signup users are counted

ROUND(trial_users * 100.0 / NULLIF(signup_users, 0), 2)

From funnel_events, output the unique user count and CVR for each step in one row. The output columns are signup_users, trial_users, purchase_users, trial_cvr, purchase_cvr.

U04 and U05 dropped before trial. U03 reached trial but did not purchase.

Expected output (one row):

trial_cvr = 3/5×100 = 60.00%, purchase_cvr = 2/3×100 = 66.67%.

SELECT
  COUNT(DISTINCT CASE WHEN step = 'signup'
    THEN user_id END)      AS signup_users,  -- unique count for the step (NULL excluded)
  COUNT(DISTINCT CASE WHEN step = 'trial_start'
    THEN user_id END)      AS trial_users,
  COUNT(DISTINCT CASE WHEN step = 'purchase'
    THEN user_id END)      AS purchase_users,
  ROUND(
    COUNT(DISTINCT CASE WHEN step = 'trial_start' THEN user_id END)
    * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN step = 'signup'
    THEN user_id END), 0), 2
  ) AS trial_cvr,     -- trial / signup conversion rate (%)
  ROUND(
    COUNT(DISTINCT CASE WHEN step = 'purchase' THEN user_id END)
    * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN step = 'trial_start'
    THEN user_id END), 0), 2
  ) AS purchase_cvr  -- purchase / trial conversion rate (%)
FROM  funnel_events;

/*
  Execution order:
  1. FROM funnel_events                   → read rows
  2. CASE WHEN step='x' THEN user_id END  → return user_id only for matching steps
  3. COUNT(DISTINCT ...)                  → skip NULL and count unique users
  4. Calculate CVR                        → compute conversion rate from the preceding step
  */

Retention rate is the KPI for the share of users who return after their first visit. D1 retention = the share of users who return the day after their first login; D7 is the share who return seven days later.

WITH first_login AS (
  SELECT user_id, MIN(login_date) AS cohort_date
  FROM  user_logins  GROUP BY user_id
)
-- detect D1/D7 returns
CASE WHEN l.login_date = f.cohort_date + 1 THEN l.user_id END
CASE WHEN l.login_date = f.cohort_date + 7 THEN l.user_id END

From user_logins, calculate D1 and D7 retention for the 2024-01-01 cohort. The output columns are cohort_date, cohort_size, d1_users, d1_retention, d7_users, d7_retention; round retention to two decimal places.

U1 and U3 returned on D1 (01-02); U2, U3, and U5 returned on D7 (01-08). U4 never returned.

Expected output (one row):

D1: U1 and U3 return on 01-02 → 2/5=40.00%. D7: U2, U3, and U5 return on 01-08 → 3/5=60.00%.

WITH first_login AS (
  SELECT
    user_id,
    MIN(login_date) AS cohort_date  -- first login date = cohort
  FROM  user_logins
  GROUP BY user_id
)
SELECT
  f.cohort_date,
  COUNT(DISTINCT f.user_id)                     AS cohort_size,
  COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1
    THEN l.user_id END)                          AS d1_users,  -- returned the next day (+1)
  ROUND(
    COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1
      THEN l.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT f.user_id), 0), 2
  )                                                 AS d1_retention,  -- D1 retention (%)
  COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 7
    THEN l.user_id END)                          AS d7_users,
  ROUND(
    COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 7
      THEN l.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT f.user_id), 0), 2
  )                                                 AS d7_retention
FROM      first_login f
LEFT JOIN user_logins l USING (user_id)  -- join all login records to the cohort
GROUP BY  f.cohort_date
ORDER BY  f.cohort_date;

/*
  Execution order:
  1. CTE first_login                         → aggregate each user's first login date
  2. LEFT JOIN user_logins                   → join all login records to the cohort
  3. CASE WHEN login_date = cohort_date + N  → detect D1/D7 returns
  4. Calculate retention                     → returning users / cohort size
  */

ARPU (Average Revenue Per User) is the KPI for average revenue per user. Comparing it across segments such as plan, channel, or region helps prioritize monetization initiatives.

ARPU = SUM(revenue) / COUNT(DISTINCT user_id)

-- use LEFT JOIN so users without purchase history are not excluded
FROM users u LEFT JOIN purchases p USING (user_id)

-- users without purchases have SUM(amount)=NULL → convert to 0 with COALESCE
COALESCE(SUM(p.amount), 0)

-- user_id is duplicated after LEFT JOIN, so DISTINCT is required
COUNT(DISTINCT u.user_id)

From the users and purchases tables, calculate user count, total revenue, and ARPU by plan. Return plan, user_count, total_revenue, arpu ordered by descending arpu. Treat revenue for users without purchases as 0, and round arpu to two decimal places.

U1 made two purchases (row expansion). U6, U7, and U8 (free) made no purchases (amount=NULL).

Expected output (arpu descending):

premium: U1(5000+3000)+U2(4500)=12500 ÷ 2 users = 6250.00. standard: U3+U4+U5=5300 ÷ 3 users = 1766.67.

SELECT
  u.plan,
  COUNT(DISTINCT u.user_id)           AS user_count,
  COALESCE(SUM(p.amount), 0)         AS total_revenue,
  ROUND(
    COALESCE(SUM(p.amount), 0) * 1.0
    / COUNT(DISTINCT u.user_id),  -- DISTINCT required
    2
  )                                   AS arpu
FROM       users     u
LEFT JOIN  purchases p  USING (user_id)
GROUP BY   u.plan
ORDER BY   arpu DESC;

/*
  Execution order:
  1. FROM users u               → read rows
  2. LEFT JOIN purchases p      → join purchases (unbought users are NULL)
  3. GROUP BY u.plan            → group by plan
  4. SUM(p.amount)              → aggregate revenue by plan
  5. COALESCE(SUM(...), 0)      → convert NULL to 0
  6. COUNT(DISTINCT u.user_id)  → count users by plan
  7. ARPU = revenue / count     → calculate average revenue per user
  8. ORDER BY arpu DESC         → output in descending order
  */