Swiggy: Genie Fare by Distance Slab — SQL Problem
Genie picks up a parcel from one address and drops it at another.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #950
Problem statement
Genie picks up a parcel from one address and drops it at another. The pricing team wants the fare of every completed task recomputed from the distance slabs to audit what customers were charged.
The fare is 49 rupees for a distance up to and including 3 km, 79 for more than 3 km up to and including 8 km, and above 8 km it is 79 plus 12 for every started kilometre beyond 8 (8.2 km pays one extra kilometre, 10.0 km pays two). Return task_id, distance_km and fare for every completed task, in any order.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: GenieTask
| Column | Type |
|---|---|
| task_id | int |
| customer_id | int |
| distance_km | decimal |
| status | enum(completed, cancelled) |
Primary key: task_id.
One row per pick-up-and-drop task. distance_km is the route length with one decimal place.
Examples
Example 1
GenieTask
| task_id | customer_id | distance_km | status |
|---|---|---|---|
| 501 | 11 | 2.4 | completed |
| 502 | 12 | 3 | completed |
| 503 | 11 | 3.1 | completed |
| 504 | 13 | 8 | completed |
| 505 | 14 | 8.2 | completed |
| 506 | 12 | 11 | completed |
| 507 | 15 | 6.5 | cancelled |
Output
| task_id | distance_km | fare |
|---|---|---|
| 501 | 2.4 | 49 |
| 502 | 3 | 49 |
| 503 | 3.1 | 79 |
| 504 | 8 | 79 |
| 505 | 8.2 | 91 |
| 506 | 11 | 115 |
How to solve Swiggy: Genie Fare by Distance Slab
The fare is a step function of the distance, which is exactly what a searched CASE expresses. Because CASE returns the first branch whose condition is true, the branches can be written as plain upper limits in increasing order: distance_km <= 3 catches the first slab, distance_km <= 8 only sees tasks that already failed the first test, and ELSE is everything above 8 km.
The boundaries are inclusive, so 3.0 km pays 49 and 8.0 km pays 79. Above 8 km the extra charge is per started kilometre: CEIL(distance_km - 8) turns 0.2 km into 1 and 3.0 km into 3. Since 8 is a whole number, CEIL(distance_km) - 8 is the same value, which is what the alternative uses together with nested IFs — MySQL's shorthand for a two-way CASE.
Cancelled tasks were never charged, so the WHERE clause leaves them out. The query is a single scan with no grouping.
Reference solution (MySQL)
SELECT task_id, distance_km,
CASE
WHEN distance_km <= 3 THEN 49
WHEN distance_km <= 8 THEN 79
ELSE 79 + 12 * CEIL(distance_km - 8)
END AS fare
FROM GenieTask
WHERE status = 'completed'Another way
SELECT task_id, distance_km, IF(distance_km <= 3, 49, IF(distance_km <= 8, 79, 79 + 12 * (CEILING(distance_km) - 8))) AS fare FROM GenieTask WHERE status = 'completed'← Swiggy: Rain Fee Collected per City · Swiggy: Saved Addresses Without a Valid Pincode →