Data Users Above Their Circle's Average — SQL Problem
Marketing wants to upsell a bigger data pack to subscribers who use more than is typical where they live.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #509
Problem statement
Marketing wants to upsell a bigger data pack to subscribers who use more than is typical where they live. A subscriber's August usage is the sum of their mb_used in August 2024; the typical figure for a circle is the average August usage of that circle's subscribers who used any data in August.
Return the subscribers whose August usage is strictly greater than the average for their own circle, with the columns subscriber_id, name, circle and total_mb. Subscribers with no August usage take no part. Return the rows in any order.
Tables
Table: Subscriber
| Column | Type |
|---|---|
| subscriber_id | int |
| name | varchar |
| circle | varchar |
Primary key: subscriber_id.
circle is the subscriber's home circle.
Table: DataUsage
| Column | Type |
|---|---|
| usage_id | int |
| subscriber_id | int |
| usage_date | date |
| mb_used | int |
Primary key: usage_id.
Mobile data used by a subscriber in one session; every subscriber_id is in Subscriber.
Examples
Example 1
Subscriber
| subscriber_id | name | circle |
|---|---|---|
| 1 | Aarav | Karnataka |
| 2 | Diya | Karnataka |
| 3 | Ishaan | Karnataka |
| 4 | Kavya | Kerala |
| 5 | Neha | Kerala |
| 6 | Rahul | Gujarat |
| 7 | Riya | Karnataka |
| 8 | Sneha | Gujarat |
DataUsage
| usage_id | subscriber_id | usage_date | mb_used |
|---|---|---|---|
| 1 | 1 | 2024-08-03 | 3000 |
| 2 | 1 | 2024-08-20 | 1200 |
| 3 | 2 | 2024-08-10 | 1500 |
| 4 | 3 | 2024-08-11 | 2100 |
| 5 | 3 | 2024-07-30 | 9000 |
| 6 | 4 | 2024-08-15 | 2000 |
| 7 | 5 | 2024-08-31 | 2000 |
| 8 | 6 | 2024-08-01 | 800 |
| 9 | 7 | 2024-09-01 | 5000 |
| 10 | 8 | 2024-08-09 | 300 |
Output
| subscriber_id | name | circle | total_mb |
|---|---|---|---|
| 1 | Aarav | Karnataka | 4200 |
| 6 | Rahul | Gujarat | 800 |
How to solve Data Users Above Their Circle's Average
The comparison is between two aggregates at different levels, so build them in order. The CTE joins subscribers to their August usage — the date range drops July and September rows — and sums per subscriber. Subscribers with no August rows vanish in the inner join, so they are neither returned nor part of any average, as the statement says.
The circle's typical usage is the average of those totals. Averaging the raw usage rows instead would weight a subscriber with ten sessions ten times and give a different number — a common mistake. The correlated subquery (SELECT AVG(o.total_mb) FROM totals o WHERE o.circle = t.circle) computes it per circle, and > is strict, so a circle where everyone used the same amount, or a circle of one, returns nobody.
A window does the same in one level: AVG(SUM(mb_used)) OVER (PARTITION BY circle) averages the grouped sums per circle, and an outer query filters on it. Both read the usage once; the subquery version re-reads the small totals table per row, which is cheap here.
Reference solution (MySQL)
WITH totals AS (
SELECT s.subscriber_id, s.name, s.circle, SUM(d.mb_used) AS total_mb
FROM Subscriber s
JOIN DataUsage d ON d.subscriber_id = s.subscriber_id
WHERE d.usage_date BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY s.subscriber_id, s.name, s.circle
)
SELECT subscriber_id, name, circle, total_mb
FROM totals t
WHERE total_mb > (SELECT AVG(o.total_mb) FROM totals o WHERE o.circle = t.circle)Another way
SELECT subscriber_id, name, circle, total_mb FROM (
SELECT s.subscriber_id, s.name, s.circle, SUM(d.mb_used) AS total_mb,
AVG(SUM(d.mb_used)) OVER (PARTITION BY s.circle) AS circle_avg
FROM Subscriber s JOIN DataUsage d ON d.subscriber_id = s.subscriber_id
WHERE d.usage_date >= '2024-08-01' AND d.usage_date < '2024-09-01'
GROUP BY s.subscriber_id, s.name, s.circle
) x
WHERE total_mb > circle_avg← Most Recharged Prepaid Plan in Each Circle · Monthly Recharge Revenue by Pack Type →