Coupon Redemption Rate
Problem
A retailer tracks promotional coupons in two tables.
```
CouponSent
+-------------+------+
| Column | Type |
+-------------+------+
| store_id | int |
| shopper_id | int |
| sent_on | date |
+-------------+------+
CouponRedeemed
+-------------+------+
| Column | Type |
+-------------+------+
| store_id | int |
| shopper_id | int |
| redeemed_on | date |
+-------------+------+
```
A coupon is identified by the `(store_id, shopper_id)` pair. The same pair may appear more than once in either table.
Report the overall **redemption rate**: the number of distinct `(store_id, shopper_id)` pairs that were redeemed divided by the number of distinct `(store_id, shopper_id)` pairs that were sent, rounded to 2 decimal places. Name the single output column `redeem_rate`. If no coupons were ever sent, the rate is 0.00.
Tables
Example rows — the live problem includes the full dataset.
| store_id | shopper_id | sent_on |
|---|---|---|
| 1 | 10 | 2023-03-01 |
| 1 | 11 | 2023-03-01 |
| 1 | 12 | 2023-03-01 |
| store_id | shopper_id | redeemed_on |
|---|---|---|
| 1 | 10 | 2023-03-03 |
| 1 | 11 | 2023-03-08 |
| 1 | 11 | 2023-03-08 |
Expected output
Your answer should return 1 row with the columns redeem_rate.
Starter code (SQL)
SELECT *
FROM CouponSent;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