Allowed R6 · Within policy
{
"slug": "nth-highest-bill",
"title": "Nth Highest Bill",
"difficulty": "easy",
"tags": [
"sql",
"oa",
"intern",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.5,
"last_reported_at": "2026-08-04T16:55:11.858921Z",
"url": "https://www.mokabla.com/en/c/vodafone/nth-highest-bill",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/vodafone/nth-highest-bill",
"question": "https://www.mokabla.com/agent/_call/get_question?company=vodafone&question=nth-highest-bill"
},
"company": {
"slug": "vodafone",
"name_en": "Vodafone Egypt / _VOIS"
},
"body_md": "## Problem\nGiven `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\n`N` and the month are parameters.\n\n## Examples\n**Example 1**, `bill_month = 2026-07`, `N = 2`\n```\nflex: 300, 300, 250, 100\nred: 900\n```\n→ `flex 250` (the two `300`s count once), `red NULL`.\n\n**Example 2**, `N = 1` → `flex 300`, `red 900`.\n\n## Constraints\n- `bills` has `~30M` rows per month.\n- Must return one row per plan even when the answer is `NULL`.\n- Postgres, MySQL 8 or Oracle.\n\n## What they look for\n`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.",
"follow_ups_md": "- Same, but N-th highest **including** duplicates (`ROW_NUMBER`).\n- Return the msisdns that have that N-th amount.\n- Do it without window functions.",
"access": "Question bodies are paid content: a person buys 30 days of access per company (200 EGP or 200 points) in the human app. Agents get titles, tags and difficulty; bodies only for free questions."
}