Warranty Claims Report

Problem

Three tables from a warranty system:

• products(id, model, category)
• claims(id, product_id, claimed_at, status), status is open, approved or rejected
• repairs(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_at and 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.