Drivers whose rating slid three weeks in a row
Driver quality managers want an early warning: a driver whose weekly average rating fell for three consecutive weeks, meaning week W is lower than W-1, W-1 is lower than W-2 and W-2 is lower than W-3 (so four consecutive weeks are involved). Weeks run Monday to Sunday and the week is identified by its Monday date (YYYY-MM-DD). Only trips with a rating count, and the weekly average is the plain average of the ratings given that week. Every driver has rated trips in every week of the data they appear in, so consecutive rows of the weekly table are consecutive calendar weeks; still, require the four weeks to span exactly 21 days.
Task: return driver (name), first_alert_week (the earliest week W that completes such a slide) and alert_weeks (how many weeks W qualify for that driver). Sort by alert_weeks from high to low, then driver A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | name TEXT | country TEXT |
|---|---|---|
| 1 | Dubai | UAE |
| 2 | Lahore | Pakistan |
| 3 | Singapore | Singapore |
| 4 | Nairobi | Kenya |
| id INTEGER | name TEXT | city_id INTEGER | joined_on TEXT | vehicle_type TEXT |
|---|---|---|---|---|
| 1 | Rashid Al Mansoori | 1 | 2025-09-18 | Economy |
| 2 | Tariq Mehmood | 2 | 2026-02-05 | Economy |
| 3 | Wei Jie Tan | 3 | 2025-09-08 | Comfort |
| 4 | Samuel Kiptoo | 4 | 2025-11-12 | Economy |
| 5 | Hassan Qureshi | 1 | 2025-12-20 | Economy |
| 6 | Priya Raman | 2 | 2025-06-24 | Economy |
Showing the first 6 of 8 rows.
| id INTEGER | name TEXT | home_city_id INTEGER | signup_date TEXT |
|---|---|---|---|
| 1 | Ayesha Khan | 1 | 2026-03-09 |
| 2 | Bilal Okafor | 2 | 2026-02-26 |
| 3 | Chen Silva | 3 | 2026-03-12 |
| 4 | Diego Tanaka | 4 | 2026-03-30 |
| 5 | Elena Weber | 1 | 2026-06-04 |
| 6 | Farid Nguyen | 2 | 2026-05-23 |
Showing the first 6 of 32 rows.
| id INTEGER | rider_id INTEGER | driver_id INTEGER | city_id INTEGER | requested_at TEXT | status TEXT | distance_km REAL | fare_usd REAL | surge REAL | rating INTEGER |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 5 | 1 | 2026-04-06 08:23:01 | Completed | 4 | 6.9 | 1 | 5 |
| 2 | 4 | 8 | 4 | 2026-04-06 10:42:25 | Completed | 13.1 | 9.44 | 1.25 | 5 |
| 3 | 3 | 3 | 3 | 2026-04-06 13:24:49 | Completed | 3.5 | 5.53 | 1 | 5 |
| 4 | 2 | 2 | 2 | 2026-04-06 17:17:07 | Completed | 15.8 | 9.5 | 1.5 | 4 |
| 5 | 1 | 1 | 1 | 2026-04-06 18:25:11 | Completed | 11.8 | 19.35 | 1.25 | 4 |
| 6 | 4 | 8 | 4 | 2026-04-06 21:01:43 | Completed | 16.6 | 9.3 | 1 | 4 |
Showing the first 6 of 548 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
date(x, 'weekday 0', '-6 days') moves a date to the Monday of its week.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.
