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 aren't 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'. Those count calendar boundaries crossed rather than full periods, so January 31 to February 1 is one month. (Other databases spell this DATEDIFF 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

Syntax reference

Need syntax details? See the SQL date functions reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

For every shipped order, show how long fulfillment took. Return order_id, order_date, shipped_date, and the number of days between them as days_to_ship. Exclude orders that haven't shipped, and show the slowest first, breaking ties by order id.