Surge Pricing Impact

ASKED IN INTERVIEW

4pts

Uber

Problem Statement

Uber operations team needs to quantify the additional revenue generated through surge pricing. A trip is considered a Surge Trip if the surge_multiplier is strictly greater than 1.0. Conversely, if the multiplier is exactly 1.0, it is a Non-Surge Trip.

Your task is to calculate the total revenue for Surge Trips, the total revenue for Non-Surge Trips, and the absolute difference between the two.

Rules:

Revenue for a trip is calculated as: fare_amount * surge_multiplier.
Surge Trip: surge_multiplier > 1.0.
Non-Surge Trip: surge_multiplier = 1.0.
Output columns: surge_revenue, non_surge_revenue, revenue_difference.
All values should be rounded to 2 decimal places.

Table Schema:

Trips: trip_id, driver_id, rider_id, fare_amount, surge_multiplier, trip_status, request_at.
Drivers: driver_id, driver_name, join_date.

Tests your understanding of

Aggregate Functions, Case When, Arithmetic Operations and Join

Input Tables

Trips

trip_id(INTEGER)	fare_amount(DECIMAL)	surge_multiplier(DECIMAL)
1	20	1.5
2	15	1
3	30	2
4	10	1
5	25	1.2
6	12	1

Expected Output

surge_revenue(DECIMAL)	non_surge_revenue(DECIMAL)	revenue_difference(DECIMAL)
120	37	83