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

Count active riders and drivers

Using the schema below, write a SQL query that returns the total number of active riders and the total number of active drivers on the platform. A user is active if users.is_active = 1. The result should be a single row with two columns: active_riders and active_drivers.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
user_type	VARCHAR(10)	NOT NULL
is_active	TINYINT	NOT NULL

Sample data:

is_active	user_id	user_type
1	1	rider
1	2	driver
0	3	driver
1	4	rider
1	5	driver

Table trips

column	type	constraints
trip_id	INT	PRIMARY KEY
driver_id	INT	NOT NULL
rider_id	INT	NOT NULL
city_id	INT	NOT NULL
trip_start_timestamp_utc	TIMESTAMP	NOT NULL
trip_status	VARCHAR(20)	NOT NULL
fare	DECIMAL(10,2)	NOT NULL

Sample data:

city_id	driver_id	fare	rider_id	trip_id	trip_start_timestamp_utc	trip_status
10	2	15.5	1	101	2025-01-05 14:00:00	completed
10	2	22	4	102	2025-06-01 18:30:00	completed
11	5	40	1	103	2025-03-15 23:00:00	completed
11	5	10	4	104	2024-12-31 23:30:00	completed
10	2	0	1	105	2025-09-02 01:00:00	canceled
10	2	10	1	106	2025-09-01 23:59:59	completed

Table city

column	type	constraints
city_id	INT	PRIMARY KEY
city_name	VARCHAR(100)	NOT NULL
timezone	VARCHAR(64)	NOT NULL

Sample data:

city_id	city_name	timezone
10	New York	America/New_York
11	Los Angeles	America/Los_Angeles

Expected output

One row containing the total number of active riders and active drivers.

active_drivers	active_riders
2	2

Hints

1. Filter on is_active = 1 to count only active users.
2. Use conditional aggregation (SUM with CASE) to count riders and drivers in separate columns.

Find the top-earning driver YTD with time zone conversion

Using the same schema and sample data, write a PostgreSQL query to return the driver_id with the highest year-to-date completed-trip earnings and total_earnings_ytd. Treat YTD as local city time from 2025-01-01 00:00:00 through 2025-09-01 23:59:59, converting trips.trip_start_timestamp_utc through city.timezone with UTC as the fallback. Only trip_status = 'completed' contributes revenue, negative fares reduce earnings, all users with user_type = 'driver' must be considered even if they have zero completed YTD trips, and ties should choose the smaller driver_id. Return exactly one row using a window-function ranking approach.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
user_type	VARCHAR(10)	NOT NULL
is_active	SMALLINT	NOT NULL

Sample data:

is_active	user_id	user_type
1	1	rider
1	2	driver
0	3	driver
1	4	rider
1	5	driver

Table trips

column	type	constraints
trip_id	INT	PRIMARY KEY
driver_id	INT	NOT NULL
rider_id	INT	NOT NULL
city_id	INT	NOT NULL
trip_start_timestamp_utc	TIMESTAMP	NOT NULL
trip_status	VARCHAR(20)	NOT NULL
fare	DECIMAL(10,2)	NOT NULL

Sample data:

city_id	driver_id	fare	rider_id	trip_id	trip_start_timestamp_utc	trip_status
10	2	15.5	1	101	2025-01-05 14:00:00	completed
10	2	22	4	102	2025-06-01 18:30:00	completed
11	5	40	1	103	2025-03-15 23:00:00	completed
11	5	10	4	104	2024-12-31 23:30:00	completed
10	2	0	1	105	2025-09-02 01:00:00	canceled
10	2	10	1	106	2025-09-01 23:59:59	completed

Table city

column	type	constraints
city_id	INT	PRIMARY KEY
city_name	VARCHAR(100)	NOT NULL
timezone	VARCHAR(64)	NOT NULL

Sample data:

city_id	city_name	timezone
10	New York	America/New_York
11	Los Angeles	America/Los_Angeles

Expected output

One row containing the driver_id with the highest 2025 YTD completed-trip earnings (in local city time) and their total_earnings_ytd. Among ties, the smallest driver_id should be returned. Drivers with zero earnings are allowed but will not win unless all drivers have zero.

driver_id	total_earnings_ytd
2	47.5

Hints

1. Convert UTC to local city time before filtering the YTD window.
2. Use a left join from the driver list so zero-earning drivers remain in the candidate set.