Skip to content
RED SAGSkills
hard60 ptsWindow FunctionsDatesAggregation

Median Delivery Days per Carrier

For each carrier, compute the median transit time in days (delivered_date - shipped_date) over delivered shipments. Shipments with a NULL delivered_date are excluded. With an even number of shipments the median is the average of the two middle values. Round with ROUND(x, 2). SQLite has no MEDIAN function.

Return columns carrier, median_days. Row order does not matter.

Schema

CREATE TABLE shipments (
  shipment_id INTEGER PRIMARY KEY,
  carrier TEXT NOT NULL,
  shipped_date TEXT NOT NULL,
  delivered_date TEXT
);

Sample data

shipments

shipment_idcarriershipped_datedelivered_date
1Aramex2026-04-012026-04-03
2Aramex2026-04-022026-04-07
3Aramex2026-04-032026-04-04
4DHL2026-04-012026-04-02
5DHL2026-04-012026-04-05
6DHL2026-04-02NULL
7FedEx2026-04-052026-04-11
Hint 1

Number the values per carrier in sorted order and attach the carrier's count with COUNT(*) OVER.

Hint 2

With integer division, (cnt + 1) / 2 and (cnt + 2) / 2 give the middle position(s) for both odd and even counts.

Hint 3

A carrier with no delivered shipments has no median, so it gets no row.

Discussion 0

Join free to post in the discussion.

Good practice for: Data Analyst, Data Scientist, Data Engineer, Business Analyst

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.