August Data Usage by Telecom Circle — SQL Problem
Each row of DataUsage is one day's mobile data for one subscriber, recorded against the circle where the data was used (a roaming subscriber shows up in…
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #500
Problem statement
Each row of DataUsage is one day's mobile data for one subscriber, recorded against the circle where the data was used (a roaming subscriber shows up in another circle).
For August 2024 only, return one row per circle with the columns circle, active_subscribers (the number of different subscribers with a usage row in that circle) and total_mb (the sum of mb_used). Order by total_mb from highest to lowest, then by circle alphabetically.
Tables
Table: DataUsage
| Column | Type |
|---|---|
| usage_id | int |
| subscriber_id | int |
| circle | varchar |
| usage_date | date |
| mb_used | int |
Primary key: usage_id.
mb_used is megabytes used that day in that circle.
Examples
Example 1
DataUsage
| usage_id | subscriber_id | circle | usage_date | mb_used |
|---|---|---|---|---|
| 1 | 101 | Karnataka | 2024-08-01 | 1500 |
| 2 | 101 | Karnataka | 2024-08-14 | 700 |
| 3 | 102 | Karnataka | 2024-08-20 | 500 |
| 4 | 103 | Kerala | 2024-07-31 | 4000 |
| 5 | 103 | Kerala | 2024-08-31 | 2500 |
| 6 | 104 | Delhi NCR | 2024-08-05 | 2500 |
| 7 | 105 | Delhi NCR | 2024-09-01 | 900 |
Output
| circle | active_subscribers | total_mb |
|---|---|---|
| Karnataka | 2 | 2700 |
| Delhi NCR | 1 | 2500 |
| Kerala | 1 | 2500 |
How to solve August Data Usage by Telecom Circle
Filter, group, aggregate. The WHERE clause keeps August 2024 — BETWEEN '2024-08-01' AND '2024-08-31' includes the first and last day, and rows from 31 July or 1 September fall outside. The rows that remain are grouped by circle.
Per circle, SUM(mb_used) is the volume and COUNT(DISTINCT subscriber_id) the number of subscribers: a subscriber with ten usage days still counts once, which plain COUNT(*) would get wrong. A roaming subscriber counts in every circle they used data in, because the grouping is by the usage row's circle.
Circles with no August rows simply have no group and do not appear. The order is by the total descending with the circle's name breaking ties. YEAR()/MONTH() or DATE_FORMAT(…, '%Y-%m') express the same filter, though a plain range on the column is the one an index on usage_date can serve. The cost is one pass plus a small sort of the circles.
Reference solution (MySQL)
SELECT circle, COUNT(DISTINCT subscriber_id) AS active_subscribers, SUM(mb_used) AS total_mb
FROM DataUsage
WHERE usage_date BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY circle
ORDER BY total_mb DESC, circleAnother way
SELECT circle, COUNT(DISTINCT subscriber_id) AS active_subscribers, SUM(mb_used) AS total_mb
FROM DataUsage
WHERE YEAR(usage_date) = 2024 AND MONTH(usage_date) = 8
GROUP BY circle
ORDER BY SUM(mb_used) DESC, circleAnother way
SELECT circle, COUNT(DISTINCT subscriber_id) AS active_subscribers, SUM(mb_used) AS total_mb
FROM DataUsage
WHERE DATE_FORMAT(usage_date, '%Y-%m') = '2024-08'
GROUP BY circle
ORDER BY total_mb DESC, circle ASC← Dormant Prepaid Subscribers Since March · Cell Tower Dropped-Call Health Check →