logo

Back to questions

Bad Delivery Rate

Hard

DoorDash's Growth Team is trying to make sure new users (those who are making orders in their first 14 days) have a great experience on all their orders in their 2 weeks on the platform.

Unfortunately, many deliveries are being messed up because:

  • the orders are being completed incorrectly (missing items, wrong order, etc.)
  • the orders aren't being received (wrong address, wrong drop off spot)
  • the orders are being delivered late (the actual delivery time is 30 minutes later than when the order was placed). Note that the estimated_delivery_timestamp is automatically set to 30 minutes after the order_timestamp.

Write a query to find the bad experience rate in the first 14 days for new users who signed up in June 2022. Output the percentage of bad experience rounded to 2 decimal places.

P.S. If you've read the Ace the Data Science Interview and liked it, consider writing us a review?

Table:

Column NameType
order_idinteger
customer_idinteger
trip_idinteger
statusstring ('completed successfully', 'completed incorrectly', 'never received')
order_timestamptimestamp

Example Input:

order_idcustomer_idtrip_idstatusorder_timestamp
7274248472100463completed successfully06/05/2022 09:12:00
2425132341100482completed incorrectly06/05/2022 14:40:00
1413671314100362completed incorrectly06/07/2022 15:03:00
5821935421100657never_received07/07/2022 15:22:00
2536131314100213completed successfully06/12/2022 13:43:00

Table:

Column NameType
dasher_idinteger
trip_idinteger
estimated_delivery_timestamptimestamp
actual_delivery_timestamptimestamp

Example Input:

dasher_idtrip_idestimated_delivery_timestampactual_delivery_timestamp
10110046306/05/2022 09:42:0006/05/2022 09:38:00
10210048206/05/2022 15:10:0006/05/2022 15:46:00
10110036206/07/2022 15:33:0006/07/2022 16:45:00
10210065707/07/2022 15:52:00-
10310021306/12/2022 14:13:0006/12/2022 14:10:00

Table:

Column NameType
customer_idinteger
signup_timestamptimestamp

Example Input:

customer_idsignup_timestamp
847205/30/2022 00:00:00
234106/01/2022 00:00:00
131406/03/2022 00:00:00
143506/05/2022 00:00:00
542106/07/2022 00:00:00

Example Output:

bad_experience_pct
75.00

Order 727424 es excluded as the order is made after the first 14 days upon signing up. There are 4 orders, however, there are only 3 orders with bad experiences as one of the orders 253613 is completed successfully. So, 3 out of 4 orders are 75% bad experience.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.