runtime
128 / 139

task Medians, shares & ratios · medium

How far do taxi trips go?

For trips with a positive trip_distance_miles, return bucket (whole miles 0–9, with 10 or more labelled 10) , trips and pct_of_trips (2 decimals).

what to return

Columns
buckettripspct_of_trips in this order
Rows
11 rows · any order
Skill
Describe a distribution with an open-ended top bucket. Refresher: Window functions: ROW_NUMBER →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result11 rows · any order
buckettripspct_of_trips
02814222.47
14111932.83
22051516.38

first 3 of 11 rows

data you can query

Mobility dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

trips19 columns · 128,620 rows
trip_idnumber · unique idvendor_idnumber · links to vendorspickup_atdate & timedropoff_atdate & timepassenger_countnumbertrip_distance_milesdecimalrate_code_idnumber · links to rate_codesstore_and_fwd_flagtextpickup_zone_idnumber · links to zonesdropoff_zone_idnumber · links to zonespayment_type_idnumber · links to payment_typesfare_amountdecimaltip_amountdecimaltolls_amountdecimalsurcharge_amountdecimalcongestion_surchargedecimalairport_feedecimalcbd_congestion_feedecimaltotal_amountdecimal
122024-12-31 21:33:432024-12-31 21:39:0021.121N179727.9002.500010.4
222025-01-01 00:01:282025-01-01 00:21:1215.511N249112125.47.476.942.52.50044.81
322025-01-01 00:02:462025-01-01 00:21:2712.981N158162118.45.8502.52.50029.25
422025-01-01 00:04:292025-01-01 00:55:58931.975N132265290020.321000111.32
522025-01-01 00:05:492025-01-01 00:20:0023.451N263107117.74.5402.52.50027.24
622025-01-01 00:06:442025-01-01 00:32:0718.041N132191235.9002.500038.4
trip_idnumber · unique id
1
vendor_idnumber · links to vendors
2
pickup_atdate & time
2024-12-31 21:33:43
dropoff_atdate & time
2024-12-31 21:39:00
passenger_countnumber
2
trip_distance_milesdecimal
1.12
rate_code_idnumber · links to rate_codes
1
store_and_fwd_flagtext
N
pickup_zone_idnumber · links to zones
179
dropoff_zone_idnumber · links to zones
7
payment_type_idnumber · links to payment_types
2
fare_amountdecimal
7.9
tip_amountdecimal
0
tolls_amountdecimal
0
surcharge_amountdecimal
2.5
congestion_surchargedecimal
0
airport_feedecimal
0
cbd_congestion_feedecimal
0
total_amountdecimal
10.4
trip_idnumber · unique id
2
vendor_idnumber · links to vendors
2
pickup_atdate & time
2025-01-01 00:01:28
dropoff_atdate & time
2025-01-01 00:21:12
passenger_countnumber
1
trip_distance_milesdecimal
5.51
rate_code_idnumber · links to rate_codes
1
store_and_fwd_flagtext
N
pickup_zone_idnumber · links to zones
249
dropoff_zone_idnumber · links to zones
112
payment_type_idnumber · links to payment_types
1
fare_amountdecimal
25.4
tip_amountdecimal
7.47
tolls_amountdecimal
6.94
surcharge_amountdecimal
2.5
congestion_surchargedecimal
2.5
airport_feedecimal
0
cbd_congestion_feedecimal
0
total_amountdecimal
44.81
trip_idnumber · unique id
3
vendor_idnumber · links to vendors
2
pickup_atdate & time
2025-01-01 00:02:46
dropoff_atdate & time
2025-01-01 00:21:27
passenger_countnumber
1
trip_distance_milesdecimal
2.98
rate_code_idnumber · links to rate_codes
1
store_and_fwd_flagtext
N
pickup_zone_idnumber · links to zones
158
dropoff_zone_idnumber · links to zones
162
payment_type_idnumber · links to payment_types
1
fare_amountdecimal
18.4
tip_amountdecimal
5.85
tolls_amountdecimal
0
surcharge_amountdecimal
2.5
congestion_surchargedecimal
2.5
airport_feedecimal
0
cbd_congestion_feedecimal
0
total_amountdecimal
29.25
trip_idnumber · unique id
4
vendor_idnumber · links to vendors
2
pickup_atdate & time
2025-01-01 00:04:29
dropoff_atdate & time
2025-01-01 00:55:58
passenger_countnumber
9
trip_distance_milesdecimal
31.97
rate_code_idnumber · links to rate_codes
5
store_and_fwd_flagtext
N
pickup_zone_idnumber · links to zones
132
dropoff_zone_idnumber · links to zones
265
payment_type_idnumber · links to payment_types
2
fare_amountdecimal
90
tip_amountdecimal
0
tolls_amountdecimal
20.32
surcharge_amountdecimal
1
congestion_surchargedecimal
0
airport_feedecimal
0
cbd_congestion_feedecimal
0
total_amountdecimal
111.32
trip_idnumber · unique id
5
vendor_idnumber · links to vendors
2
pickup_atdate & time
2025-01-01 00:05:49
dropoff_atdate & time
2025-01-01 00:20:00
passenger_countnumber
2
trip_distance_milesdecimal
3.45
rate_code_idnumber · links to rate_codes
1
store_and_fwd_flagtext
N
pickup_zone_idnumber · links to zones
263
dropoff_zone_idnumber · links to zones
107
payment_type_idnumber · links to payment_types
1
fare_amountdecimal
17.7
tip_amountdecimal
4.54
tolls_amountdecimal
0
surcharge_amountdecimal
2.5
congestion_surchargedecimal
2.5
airport_feedecimal
0
cbd_congestion_feedecimal
0
total_amountdecimal
27.24
trip_idnumber · unique id
6
vendor_idnumber · links to vendors
2
pickup_atdate & time
2025-01-01 00:06:44
dropoff_atdate & time
2025-01-01 00:32:07
passenger_countnumber
1
trip_distance_milesdecimal
8.04
rate_code_idnumber · links to rate_codes
1
store_and_fwd_flagtext
N
pickup_zone_idnumber · links to zones
132
dropoff_zone_idnumber · links to zones
191
payment_type_idnumber · links to payment_types
2
fare_amountdecimal
35.9
tip_amountdecimal
0
tolls_amountdecimal
0
surcharge_amountdecimal
2.5
congestion_surchargedecimal
0
airport_feedecimal
0
cbd_congestion_feedecimal
0
total_amountdecimal
38.4

first 6 of 128,620 rows · your queries use all of them

solution.sql
 

Hints and the answer are always available. Using them counts the question as solved with help.

How far do taxi trips go? — SQL practice · Runtime