Allowed R6 · Within policy
{
"slug": "warranty-claims-report",
"title": "Warranty Claims Report",
"difficulty": "medium",
"tags": [
"sql",
"data",
"technical",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.6,
"last_reported_at": "2026-06-02T09:54:12.710432Z",
"url": "https://www.mokabla.com/en/c/dell/warranty-claims-report",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/dell/warranty-claims-report",
"question": "https://www.mokabla.com/agent/_call/get_question?company=dell&question=warranty-claims-report"
},
"company": {
"slug": "dell",
"name_en": "Dell Technologies Egypt"
},
"body_md": "## Problem\nThree tables from a warranty system:\n\n- `products(id, model, category)`\n- `claims(id, product_id, claimed_at, status)`, status is `open`, `approved` or `rejected`\n- `repairs(claim_id, cost, completed_at)`, one row per approved claim, absent otherwise\n\nFor each product **category**, return the number of claims filed in 2026 Q1, the approval rate as a percentage with one decimal, and the average repair cost for approved claims. Categories with no claims in the quarter must still appear with zeros. Order by claim count descending.\n\n## Examples\n**Example 1**, category `Laptops` has 4 claims in Q1: 3 approved (repairs 100, 200, 300) and 1 rejected. Row: `Laptops, 4, 75.0, 200.0`.\n\n**Example 2**, category `Monitors` has one product and no claims at all. Row: `Monitors, 0, 0.0, NULL` (or `0.0` for the average if you argue it clearly).\n\n## Constraints\n- Standard SQL; SQLite or PostgreSQL syntax both accepted\n- The 2026 Q1 filter must be on `claimed_at` and must be index-friendly (no wrapping the column in a function)\n- A single query; CTEs are allowed\n\n## What they look for\nCorrect outer joins so empty categories survive, putting the date filter in the `ON` clause rather than the `WHERE` (this is the trap), integer-division awareness for the percentage, and `AVG` ignoring NULLs for unapproved claims.",
"follow_ups_md": "- Add the month-over-month change in claim count.\n- Which of these tables would you partition, and by what?\n- How would you detect duplicate claims for the same product within 7 days?",
"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."
}