Analyze Uber Supply with Joins

Context

At Uber, Operations Managers often need to evaluate supply performance across cities by combining trip activity with driver and city attributes. This requires joining a fact table with dimension tables in a way that preserves the right level of detail.

Core question

Explain how you would join trips, drivers, and cities tables to analyze supply performance in Uber. In your answer, discuss:

1. Which keys you would join on
2. Which join types you would choose and why
3. What supply metrics you would aggregate
4. How you would avoid common mistakes like double counting or dropping unmatched records

Scope guidance

The interviewer is looking for a practical SQL-oriented explanation, not just definitions of joins. You should describe the grain of each table, how to structure the query, and how to produce city-level supply metrics such as active drivers, completed trips, and utilization.