← Uber Interview Insights

Uber·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Uber. Two questions, both around the same schema, but the second one had enough edge cases to keep me busy for a while.

Questions Asked (2)

Q1

Given a users table with user_type and is_active columns, write a SQL query to return the total count of active riders and active drivers on the platform.

Product Analytics & Metrics
Author's notes

Straightforward group-by on user_type where is_active = 1.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., user_type values, what 'active' means). Then write a single query that aggregates counts conditionally using CASE or FILTER, grouping by user_type if needed, and finally present the result clearly with both counts.

Pro tip: Mention that you would validate the query by checking for NULLs or unexpected user_type values, and consider indexing is_active and user_type for performance on large tables.

1. Clarify requirements and schema

Ask about the exact values in user_type (e.g., 'rider', 'driver') and how is_active is represented (boolean, 0/1). Confirm whether 'active' means is_active = true and if there are other statuses.

2. Choose aggregation method

Decide between conditional aggregation (SUM(CASE WHEN ...)) or filtered aggregation (COUNT(*) FILTER (WHERE ...)) depending on SQL dialect. Both return counts in a single row.

3. Write the query

Construct a query that selects the count of active riders and active drivers, either as separate columns or as rows grouped by user_type. Ensure proper filtering for is_active.

4. Validate and optimize

Check for edge cases like NULLs in user_type or is_active, and suggest indexes on (user_type, is_active) to speed up the query on large datasets.

5. Present results clearly

Format the output with descriptive column aliases (e.g., active_riders, active_drivers) and explain how the query can be extended for other metrics.

Key Points to Mention

  • Use of conditional aggregation with CASE or FILTER to count multiple conditions in one query.
  • Assumption that is_active is a boolean or 0/1 flag, and user_type has distinct values for riders and drivers.
  • Handling of NULL values in user_type or is_active to avoid incorrect counts.
  • Performance considerations: indexing on (user_type, is_active) for large tables.
  • Alternative approach: GROUP BY user_type with a WHERE is_active = true, then pivot if needed.
  • Clarifying business definitions: what constitutes an 'active' user (e.g., recent activity vs. flag).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

Using the same schema (trips, users, city tables), find the driver with the highest total earnings year-to-date for 2025. YTD is defined in each city's local timezone, trip timestamps are stored in UTC, and you need to handle ties by picking the smaller driver_id. Drivers with zero qualifying trips should still appear but shouldn't win unless everyone else is also zero. Provide two versions: one with window functions and one with GROUP BY plus ORDER BY LIMIT 1.

Product Analytics & MetricsData ModelingAlgorithms & Data Structures
Author's notes

This one had layers I didn't fully see coming.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the YTD definition, emphasizing that trip timestamps in UTC must be converted to each city's local timezone before filtering for 2025. Then outline two solutions: one using a window function (e.g., RANK or ROW_NUMBER) over driver totals, and one using GROUP BY with ORDER BY and LIMIT 1, ensuring ties are broken by smaller driver_id and zero-trip drivers are included via a LEFT JOIN.

Pro tip: Mention that you would validate the timezone conversion using a known city and date, and that you would test edge cases like ties and zero-trip drivers to ensure correctness.

1. Clarify schema and requirements

Confirm the columns in trips, users, and city tables, and restate the YTD definition: trips from Jan 1, 2025, in each city's local timezone, with timestamps stored in UTC.

2. Compute earnings per driver

Join trips to city to get timezone, convert UTC timestamps to local time, filter for 2025, and aggregate total earnings per driver. Use a LEFT JOIN from users to include drivers with zero trips.

3. Implement window function solution

Use RANK() or ROW_NUMBER() over (ORDER BY total_earnings DESC, driver_id ASC) to rank drivers, then select the top-ranked driver.

4. Implement GROUP BY + ORDER BY LIMIT solution

Aggregate earnings per driver with GROUP BY, then ORDER BY total_earnings DESC, driver_id ASC and LIMIT 1 to get the top driver.

5. Handle ties and zero-trip drivers

Ensure tie-breaking by smaller driver_id in both solutions, and verify that drivers with zero qualifying trips are included but only win if all drivers have zero earnings.

Key Points to Mention

  • Timezone conversion: use city's timezone to convert UTC timestamps to local time before filtering for YTD 2025.
  • YTD definition: trips from January 1, 2025, 00:00:00 local time to the current date (or end of data) in each city's local timezone.
  • Tie-breaking: when multiple drivers have the same total earnings, pick the one with the smaller driver_id.
  • Zero-trip drivers: include all drivers via LEFT JOIN, but they should only be selected if all drivers have zero earnings.
  • Window function approach: use RANK() or ROW_NUMBER() with ORDER BY total_earnings DESC, driver_id ASC.
  • GROUP BY approach: aggregate earnings, then ORDER BY total_earnings DESC, driver_id ASC LIMIT 1.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.