August Data Usage by Telecom Circle

Easy
500.

August Data Usage by Telecom Circle

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

ColumnType
usage_idint
subscriber_idint
circlevarchar
usage_datedate
mb_usedint

Primary key: usage_id.

mb_used is megabytes used that day in that circle.

Example

The rows must come back in the order shown.

DataUsage

usage_idsubscriber_idcircleusage_datemb_used
1101Karnataka2024-08-011500
2101Karnataka2024-08-14700
3102Karnataka2024-08-20500
4103Kerala2024-07-314000
5103Kerala2024-08-312500
6104Delhi NCR2024-08-052500
7105Delhi NCR2024-09-01900

Output

circleactive_subscriberstotal_mb
Karnataka22700
Delhi NCR12500
Kerala12500
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
usage_idint
subscriber_idint
circlevarchar
usage_datedate
mb_usedint

Primary key: usage_id.

mb_used is megabytes used that day in that circle.

Examples

Example 1

DataUsage

usage_idsubscriber_idcircleusage_datemb_used
1101Karnataka2024-08-011500
2101Karnataka2024-08-14700
3102Karnataka2024-08-20500
4103Kerala2024-07-314000
5103Kerala2024-08-312500
6104Delhi NCR2024-08-052500
7105Delhi NCR2024-09-01900

Output

circleactive_subscriberstotal_mb
Karnataka22700
Delhi NCR12500
Kerala12500

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, circle

Another 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, circle

Another 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 →