Daily cancellation rate
Hardsqljoingroup bycase
trips have a status (completed or cancelled); riders marks who is banned = 1. For each day, compute rate = cancelled trips ÷ all trips, ignoring trips whose rider is banned, rounded to two decimals. Days with no trips left are omitted. Sort by day.
Tables
- trips (id INTEGER, rider_id INTEGER, status TEXT, day TEXT)
- riders (id INTEGER, banned INTEGER)
Example 1
trips
| id | rider_id | status | day |
|---|---|---|---|
| 1 | 1 | completed | 2026-01-01 |
| 2 | 2 | cancelled | 2026-01-01 |
| 3 | 3 | cancelled | 2026-01-01 |
| 4 | 1 | completed | 2026-01-02 |
riders
| id | banned |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
Expected result
| day | rate |
|---|---|
| 2026-01-01 | 0.5 |
| 2026-01-02 | 0 |
Day 1: rider 3 is banned, leaving two trips with one cancelled = 0.5.
Constraints
- 0 ≤ rows ≤ 1000
- The order of rows matters
Submit checks your solution against 7 tests, including hidden ones.
SQL
-- Write one SELECT query. Tables: trips, riders SELECT