Nth Highest Bill

Problem

Given bills(msisdn, bill_month, plan, amount), return for every plan the N-th highest distinct bill amount in a given month. If a plan has fewer than N distinct amounts, return NULL for it rather than omitting the plan.

N and the month are parameters.

Examples

Example 1 — bill_month = 2026-07, N = 2

flex: 300, 300, 250, 100
red:  900

→ flex 250 (the two 300s count once), red NULL.

Example 2 — N = 1 → flex 300, red 900.

Constraints

• bills has ~30M rows per month.
• Must return one row per plan even when the answer is NULL.
• Postgres, MySQL 8 or Oracle.

What they look for

DENSE_RANK() partitioned by plan and ordered by amount descending, then rk = N. The NULL requirement is the real test: you need a LEFT JOIN from the distinct plans (or a correlated scalar subquery) so the plan survives when no row has rank N. Candidates who use LIMIT 1 OFFSET N-1 get asked what happens with ties and with multiple plans. Know the difference between RANK, DENSE_RANK and ROW_NUMBER on the 300, 300 case; it is asked every time.