Allowed R6 · Within policy
{
"slug": "top-sellers-query",
"title": "Top Sellers Query",
"difficulty": "medium",
"tags": [
"sql",
"technical",
"backend",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.231,
"last_reported_at": "2026-04-23T15:54:40.041237Z",
"url": "https://www.mokabla.com/en/c/noon/top-sellers-query",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/noon/top-sellers-query",
"question": "https://www.mokabla.com/agent/_call/get_question?company=noon&question=top-sellers-query"
},
"company": {
"slug": "noon",
"name_en": "noon"
},
"body_md": "## Problem\nGiven these tables, return the **top 3 SKUs by revenue in each category** over the last 30 days, counting only delivered orders. Revenue is `qty * unit_price` at the time of the order.\n\n```\nproducts(sku PK, category)\norders(id PK, customer_id, created_at, status) -- status: placed | delivered | cancelled\norder_items(order_id FK, sku FK, qty, unit_price)\n```\n\nOutput columns: `category, sku, revenue`, sorted by category then revenue descending. Ties may return more than 3 rows; tell me how you would change that if the product team says strictly 3.\n\n## Examples\n**Example 1**\n```\norders: 1 delivered 2026-08-20 | 2 delivered 2026-08-25 | 3 delivered 2026-06-01 | 4 cancelled 2026-08-28\norder_items: (1,A1,1,500) (1,A2,2,300) (2,A3,1,900) (2,A1,1,500) (2,B1,3,50) (3,A4,5,100) (4,A4,9,100)\nproducts: A1..A4 phones, B1 books\n```\nAs of 2026-09-01 → `phones: A1 1000, A3 900, A2 600` and `books: B1 150`. `A4` is excluded: one order is too old, the other is cancelled.\n\n**Example 2**, a category with no delivered orders in the window returns no rows (not a row with `NULL`).\n\n## Constraints\n- `order_items` has `~200M` rows; `orders` has `~50M`. Say which indexes make this fast.\n- The \"last 30 days\" must be relative to `CURRENT_DATE`; parameterise it.\n- Postgres syntax preferred; MySQL 8 accepted.\n\n## What they look for\nA CTE that aggregates revenue per `(category, sku)`, then `DENSE_RANK()` (or `ROW_NUMBER()` if strictly 3) partitioned by category, then a filter on rank. Joining `orders` before aggregating so the status and date filter cut early. Index on `orders(status, created_at)` and `order_items(order_id)`. Candidates who write a correlated subquery for the top-N get asked to explain the cost.",
"follow_ups_md": "- Strictly 3 rows per category even on ties: `ROW_NUMBER` and a deterministic tiebreak.\n- Revenue net of refunds, where refunds live in another table.\n- This runs on every page load of the seller dashboard: what do you precompute?",
"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."
}