Daily Warranty Approval Rate
Problem
Table: `ClaimEvents`
```text
+--------------+---------+
| Column | Type |
+--------------+---------+
| customer_id | int |
| device_id | int |
| event_day | date |
| event_type | varchar |
| channel | varchar |
+--------------+---------+
There is no single-column primary key. Each row records an action on a
device. event_type is 'claim' or 'inspect'. channel is 'warranty' or 'retail'.
```
Table: `Refunds`
```text
+-----------+-----------+
| Column | Type |
+-----------+-----------+
| device_id | int |
| paid_day | date |
+-----------+-----------+
device_id is the primary key. A row means that device's warranty claim ended
in a refund.
```
For each day, look only at warranty **claim** events. Compute what percentage
of the devices claimed that day were eventually refunded. Then report the
**average of those daily percentages** across all days, rounded to 2 decimal
places. Name the single output column `avg_daily_pct`.
**Example**
```text
ClaimEvents:
+-------------+-----------+------------+------------+----------+
| customer_id | device_id | event_day | event_type | channel |
+-------------+-----------+------------+------------+----------+
| 1 | 101 | 2023-03-01 | claim | warranty |
| 2 | 102 | 2023-03-01 | claim | warranty |
| 3 | 103 | 2023-03-02 | claim | warranty |
| 4 | 104 | 2023-03-02 | inspect | warranty |
+-------------+-----------+------------+------------+----------+
Refunds:
+-----------+------------+
| device_id | paid_day |
+-----------+------------+
| 101 | 2023-03-05 |
+-----------+------------+
Output:
+---------------+
| avg_daily_pct |
+---------------+
| 25.0 |
+---------------+
```
On 2023-03-01 two devices were claimed and one refunded, so 50%. On 2023-03-02
only one device was *claimed* (the inspect row is ignored) and none refunded,
so 0%. The average of 50 and 0 is 25.0.
Tables
Example rows — the live problem includes the full dataset.
| customer_id | device_id | event_day | event_type | channel |
|---|---|---|---|---|
| 1 | 101 | 2023-03-01 | claim | warranty |
| 2 | 102 | 2023-03-01 | claim | warranty |
| 3 | 103 | 2023-03-02 | claim | warranty |
| device_id | paid_day |
|---|---|
| 101 | 2023-03-05 |
Expected output
Your answer should return 1 row with the columns avg_daily_pct.
Starter code (SQL)
SELECT *
FROM ClaimEvents;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock