Warranty Claims Report
Problem
Three tables from a warranty system:
products(id, model, category)claims(id, product_id, claimed_at, status)— status isopen,approvedorrejectedrepairs(claim_id, cost, completed_at)— one row per approved claim, absent otherwise
For 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.
Examples
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.
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).
Constraints
- Standard SQL; SQLite or PostgreSQL syntax both accepted
- The 2026 Q1 filter must be on
claimed_atand must be index-friendly (no wrapping the column in a function) - A single query; CTEs are allowed
What they look for
Correct 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.