Amazon-style Company ChallengeMediumVerified answerSQLite live

Average Delivery Time by Carrier

Return carrier, total_shipments, and avg_delivery_days for each carrier with at least 2 completed deliveries, sorted by avg_delivery_days ascending.

  • Joins
  • Aggregation
  • HAVING
  • Date analysis
  • Numeric functions

Challenge brief

Understand the request

Logistics Team is evaluating carrier performance contracts and needs a data-driven comparison of delivery speed across shipping partners.

Compare average delivery days per carrier for carriers with at least 2 completed deliveries.

Return

  • carrier
  • total_shipments
  • avg_delivery_days (rounded to 1 decimal)

Constraints

  • Delivery time is the elapsed number of days from order timestamp to delivery date
  • Exclude orders whose delivery date is unknown
  • Only carriers with 2 or more shipments qualify
  • Order by avg_delivery_days ascending

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

shipments

  • shipment_idINTEGER
  • order_idINTEGER
  • carrierVARCHAR(50)

orders

  • order_idINTEGER
  • order_dateDATETIME
  • delivery_dateDATE

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Ship date is in shipments, order date and delivery date are in orders. Join them on order_id. The day difference uses julianday() — a SQLite function that converts a date string into a decimal day count. Subtracting two julianday values gives the gap in days.

Hint 2

INNER JOIN shipments to orders on order_id. WHERE o.delivery_date IS NOT NULL excludes undelivered orders. GROUP BY s.carrier. Use ROUND(AVG(julianday(o.delivery_date) - julianday(o.order_date)), 1) AS avg_delivery_days. HAVING COUNT >= 2.

Hint 3

Scaffold: join shipments to delivered orders, group by carrier, calculate count and rounded elapsed-day average, then retain carriers meeting the sample threshold.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT s.carrier, COUNT(s.shipment_id) AS total_shipments, ROUND(AVG(julianday(o.delivery_date) - julianday(o.order_date)), 1) AS avg_delivery_days FROM shipments s INNER JOIN orders o ON s.order_id = o.order_id WHERE o.delivery_date IS NOT NULL GROUP BY s.carrier HAVING COUNT(s.shipment_id) >= 2 ORDER BY avg_delivery_days;

Why this works

julianday() converts a date string to a floating-point day number (days since November 24, 4714 BC). Subtracting two gives the exact day gap. USPS has only 1 shipment and is filtered out by HAVING COUNT >= 2.

Success check

2 carriers — FedEx (2.4 days) and UPS (2.5 days). USPS has only 1 shipment and is excluded.

Expected result

Use this output to verify values, aliases, ordering, and row count.

carriertotal_shipmentsavg_delivery_days
FedEx22.4
UPS32.5

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.