💡 Have you ever read JSON data using SQL?

If not, you’re already behind where modern data teams are headed.

Today, a lot of real-world data doesn’t come in clean relational tables. Instead, it comes as semi-structured JSON - especially in companies like Uber, Amazon, and Meta.

In this blog, we’ll break down a realistic Uber-style SQL interview problem and show you exactly how to solve it step by step.

The Business Problem

Uber wants to identify the most efficient drivers in each city.

Efficiency is defined as:

Efficiency = Fare / Trip Duration (minutes)

Simple… but the twist?

👉 The data is stored in JSON format inside a column

Table Schema

We’re working with a trips table:

column	type
trip_id	INT
driver_id	INT
trip_timestamp	DATETIME
trip_data	NVARCHAR(MAX)

Sample JSON Data

Each row contains JSON like this:

{
 "distance_km": 10,
 "duration_min": 20,
 "fare": 30,
 "pickup": {
   "city": "Toronto",
   "zone": "Downtown"
 },
 "rating": 4.8
}

Step 1: Extract JSON Fields

In SQL Server, we use JSON_VALUE to extract values:

SELECT
    driver_id,
    JSON_VALUE(trip_data, '$.pickup.city') AS city,
    CAST(JSON_VALUE(trip_data, '$.fare') AS FLOAT) AS fare,
    CAST(JSON_VALUE(trip_data, '$.duration_min') AS FLOAT) AS duration_min
FROM trips;

Step 2: Calculate Efficiency Score

fare * 1.0 / NULLIF(duration_min, 0) AS efficiency_score

Why this matters:

* 1.0 → avoids integer division
NULLIF(..., 0) → prevents divide-by-zero errors

Step 3: Aggregate per Driver

We calculate average efficiency per driver per city:

SELECT
    city,
    driver_id,
    AVG(fare * 1.0 / NULLIF(duration_min, 0)) AS avg_efficiency
FROM parsed_data
GROUP BY city, driver_id;

Step 4: Rank Drivers (FAANG-Level Step)

This is where most candidates struggle.

RANK() OVER (
    PARTITION BY city
    ORDER BY avg_efficiency DESC
) AS rank

Step 5: Get Top Drivers

WHERE rank <= 3

Final Output

city	driver_id	avg_efficiency	rank
Toronto	101	1.75	1
Toronto	102	1.40	2

What This Problem Really Tests

This is not just a SQL question.

It tests whether you understand:

✔ Working with JSON data in SQL
✔ Building derived metrics
✔ Using window functions correctly
✔ Thinking in terms of real business logic

The Real Interview Insight

Most candidates fail not because of syntax…

They fail because they don’t break the problem into steps:

Extract
Transform
Aggregate
Rank

If you follow this structure, even hard problems become manageable.

Want More Problems Like This?

If you found this useful, you’ll love my SQL Interview Practice Workbook.

It includes:

✔ 50+ real interview-style SQL problems
✔ Step-by-step solutions
✔ Focus on FAANG-level patterns
✔ Designed to build true SQL intuition (not just memorization)

👉 Get it here: https://knowstar.gumroad.com/l/gyaous

Final Thought

Learning SQL today is not just about joins and aggregations.

It’s about working with real-world messy data — like JSON, logs, and event streams.

And that’s exactly what interviewers are testing.

How to Solve an Uber SQL Interview Question Using JSON Data