Data Analyst Interview Questions & Answers: Set 5, Telecom Operator

SQL

1. 1.Find subscribers whose data usage dropped by more than 50% compared to the previous month.

   Hide answer

   Answer: Use LAG() to put each subscriber's previous month next to the current month, then keep rows where usage fell below half of the previous value. Assumed table: monthly_usage(msisdn, month, mb_used), where month is the first day of the month. Two traps: a subscriber with zero usage may have no row at all, so I check that the previous row really is the previous calendar month; and I exclude prev_mb = 0 to avoid division by zero. A drop like this is often an early churn or dual-SIM signal.

   WITH m AS (
     SELECT msisdn, month, mb_used,
            LAG(mb_used) OVER (PARTITION BY msisdn ORDER BY month) AS prev_mb,
            LAG(month)   OVER (PARTITION BY msisdn ORDER BY month) AS prev_month
     FROM monthly_usage
   )
   SELECT msisdn, month, prev_mb, mb_used,
          ROUND(100.0 * (mb_used - prev_mb) / prev_mb, 1) AS pct_change
   FROM m
   WHERE prev_month = month - INTERVAL '1 month'
     AND prev_mb > 0
     AND mb_used < 0.5 * prev_mb;

2. 2.Write a query to find churned customers: active last month but no activity this month.

   Hide answer

   Answer: Take everyone with activity last month and use NOT EXISTS to remove anyone who has any activity this month. Assumed table: activity(msisdn, activity_date) with one row per event or per active day. I use NOT EXISTS rather than NOT IN because NOT IN returns nothing if the subquery contains a NULL. In practice, run this on complete months (e.g. August vs September), because early in the current month many subscribers simply have not been active yet. Many operators also define prepaid churn as 90 days of inactivity, so confirm the business definition first.

   WITH b AS (
     SELECT DATE_TRUNC('month', CURRENT_DATE)::date AS this_m,
            (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month')::date AS last_m
   )
   SELECT DISTINCT a.msisdn
   FROM activity a CROSS JOIN b
   WHERE a.activity_date >= b.last_m
     AND a.activity_date <  b.this_m
     AND NOT EXISTS (
           SELECT 1 FROM activity x
           WHERE x.msisdn = a.msisdn
             AND x.activity_date >= b.this_m
         );