Data Analyst Interview Questions & Answers: Set 9, SaaS & Product Analytics

SQL

1. 1.Calculate monthly recurring revenue (MRR) from a subscriptions table.

   Hide answer

   Answer: MRR is the sum of normalised monthly fees from subscriptions active in each month. Assume subscriptions(sub_id, account_id, start_date, end_date, plan_price, billing_period), where end_date is NULL for active subscriptions. Generate the month list, join subscriptions that overlap each month, and convert annual plans to monthly by dividing by 12. Common trap: counting a full annual payment as one month's MRR. Also exclude one-off fees such as setup charges, because MRR is recurring revenue only.

   WITH months AS (
     SELECT generate_series(DATE '2026-01-01', DATE '2026-09-01',
                            INTERVAL '1 month')::date AS month
   )
   SELECT
     m.month,
     SUM(CASE WHEN s.billing_period = 'annual'
              THEN s.plan_price / 12.0
              ELSE s.plan_price END) AS mrr
   FROM months m
   JOIN subscriptions s
     ON s.start_date < m.month + INTERVAL '1 month'
    AND (s.end_date IS NULL OR s.end_date >= m.month)
   GROUP BY m.month
   ORDER BY m.month;

2. 2.Find users who upgraded from the free to the paid plan and how many days it took.

   Hide answer

   Answer: Find each user's first free plan start and first paid plan start, keep users where paid came after free, and subtract the dates. Assume plan_changes(user_id, plan_type, changed_at) with plan_type 'free' or 'paid'. Conditional MIN inside one GROUP BY avoids a self-join. In PostgreSQL, subtracting two dates gives whole days; on timestamps cast to date first. Users who paid straight away without a free period are excluded by the HAVING clause.

   SELECT
     user_id,
     MIN(changed_at) FILTER (WHERE plan_type = 'free') AS free_start,
     MIN(changed_at) FILTER (WHERE plan_type = 'paid') AS paid_start,
     MIN(changed_at::date) FILTER (WHERE plan_type = 'paid')
       - MIN(changed_at::date) FILTER (WHERE plan_type = 'free')
       AS days_to_upgrade
   FROM plan_changes
   GROUP BY user_id
   HAVING MIN(changed_at) FILTER (WHERE plan_type = 'paid')
        > MIN(changed_at) FILTER (WHERE plan_type = 'free')
   ORDER BY days_to_upgrade;