• For Data Analyst or Developer Post

Assumed Table

For most questions, assume you have:

rides(
    ride_id,
    driver_id,
    customer_id,
    vehicle_type,
    city,
    fare,
    ride_date,
    status
)

Where vehicle_type can be Car or Bike, and status can be Completed, Cancelled, etc.

ride_id	driver_id	customer_id	vehicle_type	city	fare	ride_date	status

101

501

1001

Bike

Delhi

250

2026-09-01

Completed

102

502

1002

Car

Delhi

600

2026-09-01

Completed

103

501

1003

Bike

Delhi

300

2026-09-02

Completed

104

503

1004

Car

Mumbai

800

2026-09-02

Completed

105

504

1005

Bike

Mumbai

350

2026-09-03

Cancelled

106

503

1001

Car

Mumbai

900

2026-09-03

Completed

107

505

1006

Bike

Delhi

200

2026-09-04

Completed

108

502

1007

Car

Delhi

700

2026-09-04

Cancelled

109

501

1001

Bike

Delhi

280

2026-09-05

Completed

110

503

1008

Car

Mumbai

850

2026-09-05

Completed

---

1. Find the Top 3 Drivers by Revenue in Each City

Question

You have:

rides(
    ride_id,
    driver_id,
    city,
    fare,
    ride_date,
    status
)

Find the top 3 drivers by total completed-ride revenue in each city.

SQL Answer

WITH driver_revenue AS (
    SELECT
        city,
        driver_id,
        SUM(fare) AS total_revenue
    FROM rides
    WHERE status = 'Completed'
    GROUP BY city, driver_id
),
ranked_drivers AS (
    SELECT
        city,
        driver_id,
        total_revenue,
        DENSE_RANK() OVER (
            PARTITION BY city
            ORDER BY total_revenue DESC
        ) AS revenue_rank
    FROM driver_revenue
)
SELECT
    city,
    driver_id,
    total_revenue,
    revenue_rank
FROM ranked_drivers
WHERE revenue_rank <= 3
ORDER BY city, revenue_rank;

Description

First, we calculate each driver’s total completed-ride revenue using SUM().

Then DENSE_RANK() ranks drivers within each city.

PARTITION BY city

means the ranking starts again for every city.

Finally, we filter for ranks 1, 2, and 3.

Interview Concept

CTE + GROUP BY + Window Functions + DENSE_RANK

---

2. Calculate Month-over-Month Revenue Growth

Question

Find the monthly revenue and calculate how much revenue increased or decreased compared with the previous month.

SQL Answer

WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(ride_date, '%Y-%m') AS ride_month,
        SUM(fare) AS revenue
    FROM rides
    WHERE status = 'Completed'
    GROUP BY DATE_FORMAT(ride_date, '%Y-%m')
),
revenue_with_previous AS (
    SELECT
        ride_month,
        revenue,
        LAG(revenue) OVER (
            ORDER BY ride_month
        ) AS previous_month_revenue
    FROM monthly_revenue
)
SELECT
    ride_month,
    revenue,
    previous_month_revenue,
    ROUND(
        (revenue - previous_month_revenue)
        / NULLIF(previous_month_revenue, 0) * 100,
        2
    ) AS growth_percentage
FROM revenue_with_previous
ORDER BY ride_month;

Description

SUM(fare) calculates revenue for each month.

The important part is:

LAG(revenue)

LAG() retrieves the revenue from the previous row, allowing us to compare the current month with the previous month.

The formula is:

(Current Revenue - Previous Revenue)
------------------------------------ × 100
Previous Revenue

NULLIF() prevents a division-by-zero error.

Interview Concept

LAG + CTE + Date Functions + Business Metrics

---

3. Find Drivers Whose Revenue Is Above the City Average

Question

Find drivers whose total revenue is greater than the average driver revenue in their city.

SQL Answer

WITH driver_revenue AS (
    SELECT
        city,
        driver_id,
        SUM(fare) AS total_revenue
    FROM rides
    WHERE status = 'Completed'
    GROUP BY city, driver_id
),
city_average AS (
    SELECT
        city,
        AVG(total_revenue) AS avg_driver_revenue
    FROM driver_revenue
    GROUP BY city
)
SELECT
    d.city,
    d.driver_id,
    d.total_revenue,
    ROUND(a.avg_driver_revenue, 2) AS city_average_revenue
FROM driver_revenue d
JOIN city_average a
    ON d.city = a.city
WHERE d.total_revenue > a.avg_driver_revenue
ORDER BY d.city, d.total_revenue DESC;

Description

We first calculate revenue for every driver.

Then we calculate the average driver revenue for each city.

Finally, we join the two datasets and select drivers whose revenue is greater than their city’s average.

This is different from comparing every driver against the company’s overall average.

Interview Concept

CTEs + Aggregation + JOIN + Comparative Analysis

---

4. Find the Second-Highest Earning Driver in Each City

Question

Find the second-highest earning driver in every city based on completed rides.

SQL Answer

WITH driver_revenue AS (
    SELECT
        city,
        driver_id,
        SUM(fare) AS total_revenue
    FROM rides
    WHERE status = 'Completed'
    GROUP BY city, driver_id
),
ranked_drivers AS (
    SELECT
        city,
        driver_id,
        total_revenue,
        DENSE_RANK() OVER (
            PARTITION BY city
            ORDER BY total_revenue DESC
        ) AS revenue_rank
    FROM driver_revenue
)
SELECT
    city,
    driver_id,
    total_revenue
FROM ranked_drivers
WHERE revenue_rank = 2;

Description

We first calculate total revenue for each driver.

Then:

DENSE_RANK() OVER (
    PARTITION BY city
    ORDER BY total_revenue DESC
)

assigns a ranking within every city.

Drivers with the highest revenue receive rank 1.

The second-highest revenue receives rank 2.

Why DENSE_RANK()?

Suppose revenues are:

Driver A   50,000
Driver B   40,000
Driver C   40,000
Driver D   30,000

DENSE_RANK() gives:

A → 1
B → 2
C → 2
D → 3

So both B and C are correctly identified as second-highest.

Interview Concept

DENSE_RANK + Window Functions

---

5. Calculate Cancellation Rate by City

Question

Calculate the following for every city:

• Total rides
• Completed rides
• Cancelled rides
• Cancellation rate

SQL Answer

SELECT
    city,
    COUNT(*) AS total_rides,

    SUM(
        CASE
            WHEN status = 'Completed' THEN 1
            ELSE 0
        END
    ) AS completed_rides,

    SUM(
        CASE
            WHEN status = 'Cancelled' THEN 1
            ELSE 0
        END
    ) AS cancelled_rides,

    ROUND(
        SUM(
            CASE
                WHEN status = 'Cancelled' THEN 1
                ELSE 0
            END
        ) * 100.0 / COUNT(*),
        2
    ) AS cancellation_rate

FROM rides
GROUP BY city
ORDER BY cancellation_rate DESC;

Description

This is an example of conditional aggregation.

For example:

CASE
    WHEN status = 'Cancelled' THEN 1
    ELSE 0
END

converts cancelled rides into 1 and everything else into 0.

SUM() then counts the cancelled rides.

The cancellation rate is:

Cancelled Rides
--------------- × 100
Total Rides

Interview Concept

CASE + SUM + Conditional Aggregation

---

6. Find Drivers Who Completed More Rides Than the Average Driver

Question

Find drivers whose number of completed rides is greater than the average number of completed rides per driver.

SQL Answer

WITH driver_rides AS (
    SELECT
        driver_id,
        COUNT(*) AS completed_rides
    FROM rides
    WHERE status = 'Completed'
    GROUP BY driver_id
),
average_rides AS (
    SELECT
        AVG(completed_rides) AS avg_completed_rides
    FROM driver_rides
)
SELECT
    d.driver_id,
    d.completed_rides,
    ROUND(a.avg_completed_rides, 2) AS average_rides
FROM driver_rides d
CROSS JOIN average_rides a
WHERE d.completed_rides > a.avg_completed_rides
ORDER BY d.completed_rides DESC;

Description

The first CTE calculates completed rides for every driver.

The second CTE calculates the average across all drivers.

Then we compare:

Driver's Completed Rides
        >
Average Completed Rides

A CROSS JOIN is used because the average value is a single value that needs to be compared with every driver.

Interview Concept

CTE + AVG + CROSS JOIN + Aggregation

---

7. Find Customers Who Took Rides on 3 Consecutive Days

Question

Find customers who completed rides on at least 3 consecutive days.

SQL Answer

WITH ride_days AS (
    SELECT DISTINCT
        customer_id,
        DATE(ride_date) AS ride_day
    FROM rides
    WHERE status = 'Completed'
),
numbered_days AS (
    SELECT
        customer_id,
        ride_day,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY ride_day
        ) AS rn
    FROM ride_days
),
grouped_days AS (
    SELECT
        customer_id,
        ride_day,
        DATE_SUB(ride_day, INTERVAL rn DAY) AS grp
    FROM numbered_days
)
SELECT
    customer_id,
    MIN(ride_day) AS start_date,
    MAX(ride_day) AS end_date,
    COUNT(*) AS consecutive_days
FROM grouped_days
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3
ORDER BY customer_id;

Description

This is a classic gaps-and-islands problem.

The ROW_NUMBER() generates:

Day          Row Number

Jan 1        1
Jan 2        2
Jan 3        3

We subtract the row number from each date.

For consecutive dates, the resulting value remains the same, allowing us to group them together.

Then:

HAVING COUNT(*) >= 3

returns customers with at least three consecutive ride days.

Interview Concept

Gaps & Islands + ROW_NUMBER + Date Arithmetic

This is one of the more advanced SQL questions an interviewer can ask.

---

8. Find the Top Vehicle Type by Revenue in Each City

Question

The company operates both cars and bikes.

Find the vehicle type that generates the highest revenue in each city.

SQL Answer

WITH vehicle_revenue AS (
    SELECT
        city,
        vehicle_type,
        SUM(fare) AS total_revenue
    FROM rides
    WHERE status = 'Completed'
    GROUP BY city, vehicle_type
),
ranked_vehicle AS (
    SELECT
        city,
        vehicle_type,
        total_revenue,
        RANK() OVER (
            PARTITION BY city
            ORDER BY total_revenue DESC
        ) AS revenue_rank
    FROM vehicle_revenue
)
SELECT
    city,
    vehicle_type,
    total_revenue
FROM ranked_vehicle
WHERE revenue_rank = 1
ORDER BY city;

Description

First, we calculate revenue by:

City + Vehicle Type

For example:

Delhi   Car     25,00,000
Delhi   Bike    18,00,000

Mumbai  Car     20,00,000
Mumbai  Bike    27,00,000

Then RANK() identifies the highest-revenue vehicle type for every city.

Interview Concept

Window Functions + PARTITION BY + Business Segmentation

---

9. Identify Drivers Whose Monthly Rides Decreased

Question

Find drivers whose completed rides decreased compared with the previous month.

SQL Answer

WITH monthly_driver_rides AS (
    SELECT
        driver_id,
        DATE_FORMAT(ride_date, '%Y-%m') AS ride_month,
        COUNT(*) AS completed_rides
    FROM rides
    WHERE status = 'Completed'
    GROUP BY
        driver_id,
        DATE_FORMAT(ride_date, '%Y-%m')
),
comparison AS (
    SELECT
        driver_id,
        ride_month,
        completed_rides,
        LAG(completed_rides) OVER (
            PARTITION BY driver_id
            ORDER BY ride_month
        ) AS previous_month_rides
    FROM monthly_driver_rides
)
SELECT
    driver_id,
    ride_month,
    completed_rides,
    previous_month_rides,
    completed_rides - previous_month_rides AS ride_change
FROM comparison
WHERE previous_month_rides IS NOT NULL
  AND completed_rides < previous_month_rides
ORDER BY driver_id, ride_month;

Description

We first calculate each driver’s monthly completed rides.

Then:

LAG(completed_rides)

gets the previous month’s rides for the same driver.

The important part is:

PARTITION BY driver_id

Without this, SQL would compare a driver against the previous row globally rather than the previous month for that particular driver.

Finally, we filter for:

completed_rides < previous_month_rides

Interview Concept

LAG + PARTITION BY + Time-Series Analysis

---

10. Find Suspicious Duplicate Ride Records

Question

The company suspects that some rides may have been recorded twice.

Find rides where the same customer and driver have rides within 5 minutes of each other with the same fare.

Assume:

rides(
    ride_id,
    driver_id,
    customer_id,
    fare,
    ride_date
)

SQL Answer

SELECT
    r1.ride_id AS ride_id_1,
    r2.ride_id AS ride_id_2,
    r1.customer_id,
    r1.driver_id,
    r1.fare,
    r1.ride_date AS ride_time_1,
    r2.ride_date AS ride_time_2
FROM rides r1
JOIN rides r2
    ON r1.customer_id = r2.customer_id
    AND r1.driver_id = r2.driver_id
    AND r1.fare = r2.fare
    AND r1.ride_id < r2.ride_id
    AND ABS(
        TIMESTAMPDIFF(
            MINUTE,
            r1.ride_date,
            r2.ride_date
        )
    ) <= 5
ORDER BY r1.customer_id, r1.ride_date;

Description

This uses a self-join, meaning the rides table is joined with itself.

We compare:

Ride 1
   ↓
Ride 2

The query looks for records where:

• Same customer
• Same driver
• Same fare
• Ride times within 5 minutes

This condition:

r1.ride_id < r2.ride_id

prevents the same pair from appearing twice.

For example, without it we could get:

Ride 101 → Ride 102
Ride 102 → Ride 101

With the condition, we only get:

Ride 101 → Ride 102

Interview Concept

Self JOIN + Date/Time Functions + Data Quality Analysis

---

Summary: What These 10 Questions Test

#	Interview Question	Main SQL Concept
1	Top 3 drivers by city	DENSE_RANK()
2	Monthly revenue growth	LAG()
3	Drivers above city average	CTE + JOIN
4	Second-highest driver	DENSE_RANK()
5	Cancellation rate	Conditional Aggregation
6	Drivers above average rides	CTE + CROSS JOIN
7	3 consecutive ride days	Gaps & Islands
8	Best vehicle type by city	RANK()
9	Monthly driver performance decline	LAG()
10	Duplicate ride detection	Self JOIN

The interviewer may also ask these follow-ups

For a Data Analyst role at a car/bike booking company, don’t stop at writing the query. Be prepared to explain:

1. Why did you use DENSE_RANK() instead of ROW_NUMBER()?
2. What happens if two drivers have the same revenue?
3. Why did you use a CTE instead of a subquery?
4. How would you optimize this query for 100 million rides?
5. What indexes would you create?
6. How would you handle NULL fares?
7. How would you calculate the same metric for Car vs Bike?
8. How would you calculate the metric weekly instead of monthly?
9. How would you identify abnormal cancellation behavior?
10. What business decision would you make from the result?

These follow-ups are often where an advanced SQL interview becomes a Data Analyst interview—the interviewer wants to see whether you can connect SQL results to actual business decisions.