Compare with the Row Before: LAG

Some questions compare a row with the one before it, like how many days passed since a customer's last order. LAG is a window function that reads a column from the row just before in its window. Like ROW_NUMBER, it keeps every row and only adds a column. The first row of a window has nothing before it, so it gets NULL.

Here each product gets the price of the product just below it in price order:

query = (
    "SELECT name, price, "
        "LAG(price) OVER (ORDER BY price) AS cheaper "
    "FROM products "
)

If you add PARTITION BY to the window, LAG starts over in each group, so a row is only compared with rows from its own group. julianday(a) - julianday(b) counts the days from b to a. If one of the dates is NULL, the days are NULL too. Put the LAG column in a WITH query, and the main query below can read it like any other column.

The hidden setup adds a third order for customer 1:

INSERT INTO orders VALUES (8, 1, '2026-05-12', 'paid')

Your query must print every order with the customer's previous order date and the days since that order:

customer_id | ordered | previous | days_since
1 | 2026-03-02 | NULL | NULL
1 | 2026-04-05 | 2026-03-02 | 34
1 | 2026-05-12 | 2026-04-05 | 37
2 | 2026-03-15 | NULL | NULL
2 | 2026-05-21 | 2026-03-15 | 67
3 | 2026-04-18 | NULL | NULL
3 | 2026-05-30 | 2026-04-18 | 42
4 | 2026-05-07 | NULL | NULL
(8 rows)

Your task: in query, write a WITH query named order_dates. It reads customer_id and ordered from orders, plus a column previous: the date of the same customer's order before this one. Below it, the main query selects customer_id, ordered, previous and the days between the two dates as days_since, from order_dates.

Assignment
Follow the Coding Tutorial to write the named query order_dates and the main query that reads it, one clause per line.

Hint
The window is OVER (PARTITION BY customer_id ORDER BY ordered): one window per customer, in date order. Put the later date first in the subtraction so the days come out positive.