ExpertWindow Functions50 points

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.

cities4 rows
id INTEGERname TEXTcountry TEXT
1DubaiUAE
2LahorePakistan
3SingaporeSingapore
4NairobiKenya
drivers8 rows
id INTEGERname TEXTcity_id INTEGERjoined_on TEXTvehicle_type TEXT
1Rashid Al Mansoori12025-09-18Economy
2Tariq Mehmood22026-02-05Economy
3Wei Jie Tan32025-09-08Comfort
4Samuel Kiptoo42025-11-12Economy
5Hassan Qureshi12025-12-20Economy
6Priya Raman22025-06-24Economy

Showing the first 6 of 8 rows.

riders32 rows
id INTEGERname TEXThome_city_id INTEGERsignup_date TEXT
1Ayesha Khan12026-03-09
2Bilal Okafor22026-02-26
3Chen Silva32026-03-12
4Diego Tanaka42026-03-30
5Elena Weber12026-06-04
6Farid Nguyen22026-05-23

Showing the first 6 of 32 rows.

trips548 rows
id INTEGERrider_id INTEGERdriver_id INTEGERcity_id INTEGERrequested_at TEXTstatus TEXTdistance_km REALfare_usd REALsurge REALrating INTEGER
11512026-04-06 08:23:01Completed46.915
24842026-04-06 10:42:25Completed13.19.441.255
33332026-04-06 13:24:49Completed3.55.5315
42222026-04-06 17:17:07Completed15.89.51.54
51112026-04-06 18:25:11Completed11.819.351.254
64842026-04-06 21:01:43Completed16.69.314

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.