Swiggy: Genie Fare by Distance Slab

Easy
950.

Swiggy: Genie Fare by Distance Slab

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

ColumnTypeValues
task_idint
customer_idint
distance_kmdecimal
statusenumcompleted, cancelled

Primary key: task_id.

One row per pick-up-and-drop task. distance_km is the route length with one decimal place.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

GenieTask

task_idcustomer_iddistance_kmstatus
501112.4completed
502123completed
503113.1completed
504138completed
505148.2completed
5061211completed
507156.5cancelled

Output

task_iddistance_kmfare
5012.449
502349
5033.179
504879
5058.291
50611115
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
task_idint
customer_idint
distance_kmdecimal
statusenum(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_idcustomer_iddistance_kmstatus
501112.4completed
502123completed
503113.1completed
504138completed
505148.2completed
5061211completed
507156.5cancelled

Output

task_iddistance_kmfare
5012.449
502349
5033.179
504879
5058.291
50611115

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 →