Write SQL for active counts and YTD top driver

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 | +---------+-------------+----------------------+