Top Two Data Consumers per Circle, Ties Included
For the September loyalty campaign, each circle rewards the subscribers whose September 2024 data usage is among the two highest distinct totals of that…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #521
Problem statement
For the September loyalty campaign, each circle rewards the subscribers whose September 2024 data usage is among the two highest distinct totals of that circle. Equal totals share a place, so a circle with totals 9000, 9000, 7500, 6000 rewards three subscribers (two at place 1, one at place 2).
A subscriber's total is the sum of their mb_used dated in September 2024; subscribers with no September usage take no part. Return circle, subscriber_id, name, total_mb and circle_rank (1 or 2). Order by circle, then circle_rank, then subscriber_id.
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.
Data used 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 | Karnataka |
| 5 | Neha | Kerala |
| 6 | Rahul | Kerala |
| 7 | Riya | Kerala |
DataUsage
| usage_id | subscriber_id | usage_date | mb_used |
|---|---|---|---|
| 1 | 1 | 2024-09-02 | 6000 |
| 2 | 1 | 2024-09-15 | 3000 |
| 3 | 2 | 2024-09-30 | 9000 |
| 4 | 3 | 2024-09-10 | 7500 |
| 5 | 4 | 2024-09-12 | 6000 |
| 6 | 4 | 2024-08-31 | 8000 |
| 7 | 5 | 2024-09-01 | 4000 |
| 8 | 6 | 2024-10-01 | 9500 |
| 9 | 7 | 2024-09-20 | 2500 |
Output
| circle | subscriber_id | name | total_mb | circle_rank |
|---|---|---|---|---|
| Karnataka | 1 | Aarav | 9000 | 1 |
| Karnataka | 2 | Diya | 9000 | 1 |
| Karnataka | 3 | Ishaan | 7500 | 2 |
| Kerala | 5 | Neha | 4000 | 1 |
| Kerala | 7 | Riya | 2500 | 2 |
How to solve Top Two Data Consumers per Circle, Ties Included
First aggregate: join subscribers to their usage, keep September 2024 (BETWEEN '2024-09-01' AND '2024-09-30' — the 31 August and 1 October rows stay out), and sum per subscriber, carrying the home circle along in the GROUP BY.
Then rank inside each circle. The choice of ranking function is the whole problem. DENSE_RANK() gives equal totals the same number and continues with the next integer, so ranks 1 and 2 are exactly the two highest distinct totals, however many subscribers share them. RANK() would jump from 1 to 3 after a tie at the top and lose the second place; ROW_NUMBER() would break ties arbitrarily and drop a winner. Window results cannot be filtered in the same SELECT, so the rank is computed in a CTE and circle_rank <= 2 is applied outside.
The correlated alternative computes the place directly — one plus the number of distinct totals in the same circle that are higher — which is the definition of a dense rank. It is quadratic in a circle's size but needs no window functions.
Reference solution (MySQL)
WITH totals AS (
SELECT s.circle, s.subscriber_id, s.name, 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-09-01' AND '2024-09-30'
GROUP BY s.circle, s.subscriber_id, s.name
), ranked AS (
SELECT circle, subscriber_id, name, total_mb,
DENSE_RANK() OVER (PARTITION BY circle ORDER BY total_mb DESC) AS circle_rank
FROM totals
)
SELECT circle, subscriber_id, name, total_mb, circle_rank
FROM ranked
WHERE circle_rank <= 2
ORDER BY circle, circle_rank, subscriber_idAnother way
WITH totals AS (
SELECT s.circle, s.subscriber_id, s.name, SUM(d.mb_used) AS total_mb
FROM Subscriber s JOIN DataUsage d ON d.subscriber_id = s.subscriber_id
WHERE d.usage_date >= '2024-09-01' AND d.usage_date < '2024-10-01'
GROUP BY s.circle, s.subscriber_id, s.name
), placed AS (
SELECT t.*, 1 + (SELECT COUNT(DISTINCT o.total_mb) FROM totals o
WHERE o.circle = t.circle AND o.total_mb > t.total_mb) AS circle_rank
FROM totals t
)
SELECT circle, subscriber_id, name, total_mb, circle_rank FROM placed
WHERE circle_rank <= 2
ORDER BY circle, circle_rank, subscriber_id← Daily Network Drop Rate Including Silent Days · Electricity Bill Settlement From Part Payments →