Top Two Data Consumers per Circle, Ties Included

Hard
521.

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

ColumnType
subscriber_idint
namevarchar
circlevarchar

Primary key: subscriber_id.

circle is the subscriber's home circle.

Table: DataUsage

ColumnType
usage_idint
subscriber_idint
usage_datedate
mb_usedint

Primary key: usage_id.

Data used in one session; every subscriber_id is in Subscriber.

Example

The rows must come back in the order shown.

Subscriber

subscriber_idnamecircle
1AaravKarnataka
2DiyaKarnataka
3IshaanKarnataka
4KavyaKarnataka
5NehaKerala
6RahulKerala
7RiyaKerala

DataUsage

usage_idsubscriber_idusage_datemb_used
112024-09-026000
212024-09-153000
322024-09-309000
432024-09-107500
542024-09-126000
642024-08-318000
752024-09-014000
862024-10-019500
972024-09-202500

Output

circlesubscriber_idnametotal_mbcircle_rank
Karnataka1Aarav90001
Karnataka2Diya90001
Karnataka3Ishaan75002
Kerala5Neha40001
Kerala7Riya25002
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.

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

ColumnType
subscriber_idint
namevarchar
circlevarchar

Primary key: subscriber_id.

circle is the subscriber's home circle.

Table: DataUsage

ColumnType
usage_idint
subscriber_idint
usage_datedate
mb_usedint

Primary key: usage_id.

Data used in one session; every subscriber_id is in Subscriber.

Examples

Example 1

Subscriber

subscriber_idnamecircle
1AaravKarnataka
2DiyaKarnataka
3IshaanKarnataka
4KavyaKarnataka
5NehaKerala
6RahulKerala
7RiyaKerala

DataUsage

usage_idsubscriber_idusage_datemb_used
112024-09-026000
212024-09-153000
322024-09-309000
432024-09-107500
542024-09-126000
642024-08-318000
752024-09-014000
862024-10-019500
972024-09-202500

Output

circlesubscriber_idnametotal_mbcircle_rank
Karnataka1Aarav90001
Karnataka2Diya90001
Karnataka3Ishaan75002
Kerala5Neha40001
Kerala7Riya25002

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_id

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