runtimetask NULL handling · medium
Debug: observable tip rate
Return pickup_zone_id and tip_pct (tip as a percentage of fare, 2 decimals) for zones with at least 500 card trips. The query includes cash trips, whose tips are never recorded. Fix it.
what to return
- Columns
pickup_zone_idtip_pctin this order- Rows
- 41 rows · any order
- Skill
- Repair the query’s logic and confirm the fix on changed data. Refresher: Ratios, ROUND & NULLIF →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result41 rows · any order
| pickup_zone_id | tip_pct |
|---|---|
| 13 | 22.91 |
| 43 | 23.76 |
| 48 | 25.13 |
first 3 of 41 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 id | vendor_idnumber · links to vendors | pickup_atdate & time | dropoff_atdate & time | passenger_countnumber | trip_distance_milesdecimal | rate_code_idnumber · links to rate_codes | store_and_fwd_flagtext | pickup_zone_idnumber · links to zones | dropoff_zone_idnumber · links to zones | payment_type_idnumber · links to payment_types | fare_amountdecimal | tip_amountdecimal | tolls_amountdecimal | surcharge_amountdecimal | congestion_surchargedecimal | airport_feedecimal | cbd_congestion_feedecimal | total_amountdecimal |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 2024-12-31 21:33:43 | 2024-12-31 21:39:00 | 2 | 1.12 | 1 | N | 179 | 7 | 2 | 7.9 | 0 | 0 | 2.5 | 0 | 0 | 0 | 10.4 |
| 2 | 2 | 2025-01-01 00:01:28 | 2025-01-01 00:21:12 | 1 | 5.51 | 1 | N | 249 | 112 | 1 | 25.4 | 7.47 | 6.94 | 2.5 | 2.5 | 0 | 0 | 44.81 |
| 3 | 2 | 2025-01-01 00:02:46 | 2025-01-01 00:21:27 | 1 | 2.98 | 1 | N | 158 | 162 | 1 | 18.4 | 5.85 | 0 | 2.5 | 2.5 | 0 | 0 | 29.25 |
| 4 | 2 | 2025-01-01 00:04:29 | 2025-01-01 00:55:58 | 9 | 31.97 | 5 | N | 132 | 265 | 2 | 90 | 0 | 20.32 | 1 | 0 | 0 | 0 | 111.32 |
| 5 | 2 | 2025-01-01 00:05:49 | 2025-01-01 00:20:00 | 2 | 3.45 | 1 | N | 263 | 107 | 1 | 17.7 | 4.54 | 0 | 2.5 | 2.5 | 0 | 0 | 27.24 |
| 6 | 2 | 2025-01-01 00:06:44 | 2025-01-01 00:32:07 | 1 | 8.04 | 1 | N | 132 | 191 | 2 | 35.9 | 0 | 0 | 2.5 | 0 | 0 | 0 | 38.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.