Task
You are given Lyft marketplace data and asked to compare performance across regions and time periods. Write a SQL query that returns, for each region and calendar month in Q1 2024, total completed rides, total gross bookings, average driver rating, and the month-over-month change in completed rides. Include only regions with at least 2 completed rides in the month, and label each row as improving, declining, or flat_or_new based on the completed-ride change versus the prior month.
Schema
lyft_regions
| column | type | description |
|---|
| region_id | INT | Region identifier |
| region_name | VARCHAR(50) | Lyft region name |
| country_code | VARCHAR(2) | Country code |
| | |
lyft_drivers
| column | type | description |
|---|
| driver_id | INT | Driver identifier |
| region_id | INT | Driver's home region |
| driver_name | VARCHAR(100) | Driver name |
| signup_date | DATE | Driver signup date |
| | |
lyft_rides
| column | type | description |
|---|
| ride_id | INT | Ride identifier |
| driver_id | INT | Driver who completed or accepted the ride |
| ride_date | DATE | Ride date |
| status | VARCHAR(20) | Ride status |
| gross_bookings | DECIMAL(10,2) | Booking value |
| driver_rating | DECIMAL(3,2) | Rider-submitted driver rating |
Sample data
Representative rows:
| ride_id | driver_id | ride_date | status | gross_bookings | driver_rating |
|---|
| 1003 | 101 | 2024-01-20 | completed | 18.00 | 4.70 |
| 1008 | 103 | 2024-02-14 | completed | 30.00 | 4.95 |
| 1013 | 106 | 2024-03-01 | completed | 0.00 | 4.20 |
| 1016 | 999 | 2024-03-18 | completed | 25.00 | 4.50 |
Expected output
| month | region_name | completed_rides | total_gross_bookings | avg_driver_rating | completed_rides_mom_change | performance_trend |
|---|
| 2024-01 | Chicago | 2 | 33.00 | 4.45 | null | flat_or_new |
| 2024-02 | Chicago | 2 | 42.00 | 4.55 | 0 | flat_or_new |
| 2024-03 | Chicago | 3 | 47.00 | 4.43 | 1 | improving |
