Write SQL for active counts and YTD top driver
Company: Uber
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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 |
+---------+-------------+----------------------+
Overview: This question evaluates a data scientist's SQL competency in aggregation, joins, time zone–aware datetime conversion, filtering on trip status, handling negative fares and zero-value drivers, tie-breaking, and the comparative use of window functions versus GROUP BY for ranking.
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.
Tables
users(user_id INT, user_type VARCHAR(10), is_active TINYINT)
trips(trip_id INT, driver_id INT, rider_id INT, city_id INT, trip_start_timestamp_utc TIMESTAMP, trip_status VARCHAR(20), fare DECIMAL(10,2))
city(city_id INT, city_name VARCHAR(100), timezone VARCHAR(64))
Hints
- Filter on is_active = 1 to count only active users.
- 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.
Tables
users(user_id INT, user_type VARCHAR(10), is_active SMALLINT)
trips(trip_id INT, driver_id INT, rider_id INT, city_id INT, trip_start_timestamp_utc TIMESTAMP, trip_status VARCHAR(20), fare DECIMAL(10,2))
city(city_id INT, city_name VARCHAR(100), timezone VARCHAR(64))
Hints
- Convert UTC to local city time before filtering the YTD window.
- Use a left join from the driver list so zero-earning drivers remain in the candidate set.