Given the following schema and sample data, write SQL to: (a) return the total count of active riders and active drivers on the platform; (b) return the driver_id with the highest total earnings year-to-date (YTD), along with their total_earnings_ytd. Treat a trip as revenue-contributing only if trip_status = 'completed'. Allow negative fares to reduce earnings (refunds/adjustments). Define YTD as 2025-01-01 00:00:00 through 2025-09-01 23:59:59 in the LOCAL time zone of the trip’s city; trips.trip_start_timestamp_utc is stored in UTC and must be converted using city.timezone. If a city has no matching timezone row, treat its trips as UTC. Break ties on total_earnings_ytd by choosing the smaller driver_id. Provide two solutions for part (b): one using window functions and one using GROUP BY + ORDER BY LIMIT 1. Also ensure your query doesn’t drop drivers with zero 2025 YTD completed trips (they should not win unless everyone is zero). Schema and sample data:
-
users(user_id INT, user_type STRING IN ('rider','driver'), is_active TINYINT)
-
trips(trip_id INT, driver_id INT, rider_id INT, city_id INT, trip_start_timestamp_utc TIMESTAMP, trip_status STRING, fare DECIMAL(10,2))
-
city(city_id INT, city_name STRING, timezone STRING e.g., 'America/New_York')
Sample tables:
users
+---------+-----------+-----------+
| user_id | user_type | is_active |
+---------+-----------+-----------+
| 1 | rider | 1 |
| 2 | driver | 1 |
| 3 | driver | 0 |
| 4 | rider | 1 |
| 5 | driver | 1 |
+---------+-----------+-----------+
trips
+---------+-----------+----------+---------+---------------------------+-------------+-------+
| trip_id | driver_id | rider_id | city_id | trip_start_timestamp_utc | trip_status | fare |
+---------+-----------+----------+---------+---------------------------+-------------+-------+
| 101 | 2 | 1 | 10 | 2025-01-05 14:00:00 | completed | 15.50 |
| 102 | 2 | 4 | 10 | 2025-06-01 18:30:00 | completed | 22.00 |
| 103 | 5 | 1 | 11 | 2025-03-15 23:00:00 | completed | 40.00 |
| 104 | 5 | 4 | 11 | 2024-12-31 23:30:00 | completed | 10.00 |
| 105 | 2 | 1 | 10 | 2025-09-02 01:00:00 | canceled | 0.00 |
| 106 | 2 | 1 | 10 | 2025-09-01 23:59:59 | completed | 10.00 |
+---------+-----------+----------+---------+---------------------------+-------------+-------+
city
+---------+-------------+----------------------+
| city_id | city_name | timezone |
+---------+-------------+----------------------+
| 10 | New York | America/New_York |
| 11 | Los Angeles | America/Los_Angeles |
+---------+-------------+----------------------+