Data Analyst Role in Telecom What the Job Really Looks Like

-- Monthly ARPU by circle (aggregate first, then divide)
WITH rev AS (
    SELECT circle, DATE_TRUNC('month', txn_date) AS month,
           SUM(amount) AS revenue
    FROM recharges
    GROUP BY circle, DATE_TRUNC('month', txn_date)
),
active AS (
    SELECT circle, DATE_TRUNC('month', usage_date) AS month,
           COUNT(DISTINCT subscriber_id) AS active_users
    FROM daily_usage
    GROUP BY circle, DATE_TRUNC('month', usage_date)
)
SELECT r.circle, r.month,
       ROUND(r.revenue / NULLIF(a.active_users, 0), 2) AS arpu
FROM rev r
JOIN active a USING (circle, month)
ORDER BY r.month, r.circle;

-- Users who moved from one plan to another between two months
SELECT prev.plan_id AS from_plan,
       curr.plan_id AS to_plan,
       COUNT(*)     AS users
FROM subscriber_plan_month prev
JOIN subscriber_plan_month curr
  ON curr.subscriber_id = prev.subscriber_id
 AND curr.month = prev.month + INTERVAL '1 month'
WHERE prev.month = DATE '2026-09-01'
  AND prev.plan_id <> curr.plan_id
GROUP BY prev.plan_id, curr.plan_id
ORDER BY users DESC;

-- Flag subscribers with no usage in the last 30 days
SELECT s.subscriber_id,
       s.circle,
       MAX(u.usage_date) AS last_active,
       CURRENT_DATE - MAX(u.usage_date) AS days_inactive
FROM subscribers s
LEFT JOIN daily_usage u ON u.subscriber_id = s.subscriber_id
GROUP BY s.subscriber_id, s.circle
HAVING MAX(u.usage_date) IS NULL
    OR CURRENT_DATE - MAX(u.usage_date) > 30;

import pandas as pd

u = pd.read_csv("monthly_usage.csv")   # subscriber_id, month, data_gb
u = u.sort_values(["subscriber_id", "month"])
u["prev_gb"] = u.groupby("subscriber_id")["data_gb"].shift(1)
u["drop_pct"] = (u["prev_gb"] - u["data_gb"]) / u["prev_gb"] * 100

at_risk = u[(u["prev_gb"] >= 5) & (u["drop_pct"] >= 50)]
print(at_risk[["subscriber_id", "month", "prev_gb", "data_gb"]].head())

-- Sites with highest call drop rate in peak hours (minimum volume)
SELECT site_id,
       city,
       SUM(dropped_calls)   AS dropped,
       SUM(connected_calls) AS connected,
       ROUND(100.0 * SUM(dropped_calls) / NULLIF(SUM(connected_calls), 0), 2) AS drop_rate_pct
FROM site_hourly_kpi
WHERE hour_of_day BETWEEN 18 AND 22
  AND kpi_date >= CURRENT_DATE - 7
GROUP BY site_id, city
HAVING SUM(connected_calls) >= 1000
ORDER BY drop_rate_pct DESC
LIMIT 20;

-- Combine network KPIs with complaints for prioritisation
SELECT k.site_id,
       k.drop_rate_pct,
       COUNT(c.ticket_id) AS network_complaints
FROM weekly_site_kpi k
LEFT JOIN complaints c
  ON c.site_id = k.site_id
 AND c.category = 'NETWORK'
 AND c.created_at >= CURRENT_DATE - 7
GROUP BY k.site_id, k.drop_rate_pct
ORDER BY network_complaints DESC, k.drop_rate_pct DESC;

subscribers(subscriber_id, circle, plan_id, type, activated_on)
recharges(subscriber_id, txn_date, amount, plan_id)
daily_usage(subscriber_id, usage_date, data_mb, voice_min)
site_hourly_kpi(site_id, city, kpi_date, hour_of_day,
                connected_calls, dropped_calls)
complaints(ticket_id, subscriber_id, site_id, category,
           created_at)
Generate 3 months of synthetic rows with Python.

Page 1 — Revenue: ARPU by circle, plan mix trend
Page 2 — Customers: inactive users, churn trend,
         at-risk list with usage drop
Page 3 — Network: worst 20 sites at peak hours,
         complaints vs drop rate scatter
Summary card: 3 insights + 3 recommended actions

Telecom weekly note — Week __
Revenue: ARPU change vs last week, by circle
Customers: inactive users added; top risk segment
Network: top 3 sites to fix and why
Recommendation: one action per area