Most Recharged Prepaid Plan in Each Circle

Medium
508.

Most Recharged Prepaid Plan in Each Circle

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

ColumnType
recharge_idint
subscriber_idint
circlevarchar
plan_codevarchar
amountint
recharge_datedate

Primary key: recharge_id.

One row per recharge; plan_code names the plan (P299 is the ₹299 plan).

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Recharge

recharge_idsubscriber_idcircleplan_codeamountrecharge_date
11KarnatakaP2992992024-04-02
22KarnatakaP2992992024-04-05
33KarnatakaP7197192024-04-07
44KeralaP2992992024-04-08
55KeralaP5795792024-04-09
66Delhi NCRP98982024-04-10
76Delhi NCRP98982024-04-20
87Delhi NCRP2992992024-04-21
98Delhi NCRP98982024-04-22
109Delhi NCRP2992992024-04-25

Output

circleplan_coderecharges
Delhi NCRP983
KarnatakaP2992
KeralaP5791
KeralaP2991
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.

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

ColumnType
recharge_idint
subscriber_idint
circlevarchar
plan_codevarchar
amountint
recharge_datedate

Primary key: recharge_id.

One row per recharge; plan_code names the plan (P299 is the ₹299 plan).

Examples

Example 1

Recharge

recharge_idsubscriber_idcircleplan_codeamountrecharge_date
11KarnatakaP2992992024-04-02
22KarnatakaP2992992024-04-05
33KarnatakaP7197192024-04-07
44KeralaP2992992024-04-08
55KeralaP5795792024-04-09
66Delhi NCRP98982024-04-10
76Delhi NCRP98982024-04-20
87Delhi NCRP2992992024-04-21
98Delhi NCRP98982024-04-22
109Delhi NCRP2992992024-04-25

Output

circleplan_coderecharges
Delhi NCRP983
KarnatakaP2992
KeralaP5791
KeralaP2991

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

Another 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.recharges

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