Most Recharged Prepaid Plan in Each Circle — SQL Problem
Product managers decide which prepaid plan to feature on each circle's home screen by how often it is bought there.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #508
Problem statement
Product managers decide which prepaid plan to feature on each circle's home screen by how often it is bought there.
For each circle, return the plan or plans with the most recharges in that circle, with the columns circle, plan_code and recharges (the number of recharge rows for that plan in that circle). When several plans tie for the top count in a circle, return all of them. Return the rows in any order.
Tables
Table: Recharge
| Column | Type |
|---|---|
| recharge_id | int |
| subscriber_id | int |
| circle | varchar |
| plan_code | varchar |
| amount | int |
| recharge_date | date |
Primary key: recharge_id.
One row per recharge; plan_code names the plan (P299 is the ₹299 plan).
Examples
Example 1
Recharge
| recharge_id | subscriber_id | circle | plan_code | amount | recharge_date |
|---|---|---|---|---|---|
| 1 | 1 | Karnataka | P299 | 299 | 2024-04-02 |
| 2 | 2 | Karnataka | P299 | 299 | 2024-04-05 |
| 3 | 3 | Karnataka | P719 | 719 | 2024-04-07 |
| 4 | 4 | Kerala | P299 | 299 | 2024-04-08 |
| 5 | 5 | Kerala | P579 | 579 | 2024-04-09 |
| 6 | 6 | Delhi NCR | P98 | 98 | 2024-04-10 |
| 7 | 6 | Delhi NCR | P98 | 98 | 2024-04-20 |
| 8 | 7 | Delhi NCR | P299 | 299 | 2024-04-21 |
| 9 | 8 | Delhi NCR | P98 | 98 | 2024-04-22 |
| 10 | 9 | Delhi NCR | P299 | 299 | 2024-04-25 |
Output
| circle | plan_code | recharges |
|---|---|---|
| Delhi NCR | P98 | 3 |
| Karnataka | P299 | 2 |
| Kerala | P579 | 1 |
| Kerala | P299 | 1 |
How to solve Most Recharged Prepaid Plan in Each Circle
Two steps: aggregate, then pick the top of each group. Grouping by (circle, plan_code) and counting gives each plan's popularity per circle.
To keep the leaders, rank the counts within each circle — a window over the grouped rows, RANK() OVER (PARTITION BY circle ORDER BY COUNT(*) DESC), is allowed in the same SELECT because window functions run after GROUP BY. RANK gives tied counts the same rank, so every plan sharing the top count gets rank 1, as the statement requires; ROW_NUMBER would pick one of them arbitrarily and make the answer depend on storage order.
Without windows, compute each circle's maximum count in a second CTE and join it back, or keep a count when no other plan of the same circle has a higher one (NOT EXISTS). All three read the table once to aggregate; the remaining work is over the small table of counts.
Reference solution (MySQL)
WITH counts AS (
SELECT circle, plan_code, COUNT(*) AS recharges,
RANK() OVER (PARTITION BY circle ORDER BY COUNT(*) DESC) AS rk
FROM Recharge
GROUP BY circle, plan_code
)
SELECT circle, plan_code, recharges
FROM counts
WHERE rk = 1Another way
WITH counts AS (
SELECT circle, plan_code, COUNT(*) AS recharges FROM Recharge GROUP BY circle, plan_code
), best AS (
SELECT circle, MAX(recharges) AS top FROM counts GROUP BY circle
)
SELECT c.circle, c.plan_code, c.recharges
FROM counts c JOIN best b ON b.circle = c.circle AND b.top = c.rechargesAnother way
WITH counts AS (
SELECT circle, plan_code, COUNT(*) AS recharges FROM Recharge GROUP BY circle, plan_code
)
SELECT circle, plan_code, recharges FROM counts c
WHERE NOT EXISTS (SELECT 1 FROM counts o WHERE o.circle = c.circle AND o.recharges > c.recharges)← Units Consumed From Cumulative Meter Readings · Data Users Above Their Circle's Average →