Lesson 7.2

SQL Date Math and Lead Times

Subtracting dates answers real questions: how long did shipping take, how old is this record?

Dates are not just for filtering. The distance between two of them is often the number you actually want: lead time, age, time-to-resolve.

DuckDB's date_diff('day', start, end) returns the whole number of days from one date to another. The first argument is the unit, so you can switch to 'month' or 'year' without changing anything else. (Other databases spell thisDATEDIFF or just subtract the dates.)

Pattern

select ticket_id,
       date_diff('day', opened_at, closed_at) as days_open
from tickets
where closed_at is not null

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text

Your task

For every shipped order, show how long fulfilment took. Return order_id, order_date,shipped_date, and the number of days between them asdays_to_ship. Exclude orders that haven't shipped, and show the slowest first.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer