Surge share by city

conditional logic

Surge share by city

Lyft SQL Interview Question

Lyft's pricing team tracks how often riders in each city see surge pricing, meaning a multiplier above 1.0.

For each city, return the number of ride requests as requests and the percentage of requests with surge pricing as surge_pct, rounded to 1 decimal place. Sort by surge_pct, highest first.

Asked of

  • Data Analyst
  • Business Analyst
  • BI Analyst
  • Product Analyst
  • Analytics Engineer

ride_requestsTable22 rows

Column NameType
request_idBIGINT
cityVARCHAR
requested_atTIMESTAMP
surge_multiplierDOUBLE

ride_requestsExample Input

request_idcityrequested_atsurge_multiplier
1San Francisco2024-09-06 08:05:001
2Chicago2024-09-06 07:45:001
3San Francisco2024-09-06 08:20:001.2
4Miami2024-09-06 21:10:001
5San Francisco2024-09-06 17:40:001.5
6Chicago2024-09-06 17:05:001.3
7Miami2024-09-06 22:15:001.4
8San Francisco2024-09-06 08:45:001.2
9Chicago2024-09-06 17:30:001.3
10Miami2024-09-06 22:40:001.6
11San Francisco2024-09-06 18:10:001.1
12Chicago2024-09-06 08:15:001
13Miami2024-09-06 22:55:001.8
14San Francisco2024-09-07 08:30:001
15Chicago2024-09-07 17:50:001.2
16Miami2024-09-07 13:05:001
17San Francisco2024-09-07 17:15:001.4
18Chicago2024-09-07 12:00:001
19Miami2024-09-07 22:20:001.5
20Miami2024-09-07 21:45:001.1
21Chicago2024-09-07 07:10:001
22San Francisco2024-09-07 12:25:001

Example Output

cityrequestssurge_pct
Miami771.4
San Francisco862.5
Chicago742.9

Explanation

San Francisco had 8 requests, and 5 of them had a multiplier above 1.0, so its surge share is 62.5. Requests with a multiplier of exactly 1.0 are normal price and do not count as surge.

The example above is a small slice of the data. Your query runs against the full tables.