Days Until the Second Order in Python

An interviewer asks: how many days pass between each customer's first and second order? Customers with only one order still belong in the answer, with NULL for the second date.

LEAD(column) OVER (…) reads a column from the next row of the same window; the last row gets NULL. Without PARTITION BY the window is the whole table. For example, this puts each order next to the date of the shop's next order, whoever placed it:

SELECT id, ordered,
    LEAD(ordered) OVER (ORDER BY ordered) AS next_in_shop
FROM orders

Partition the window by customer and add a ROW_NUMBER over the same window: on row 1 sits a customer's first order, and LEAD puts their second order's date beside it. Dates are text in SQLite, so you can't subtract them directly: julianday('2026-06-10') - julianday('2026-06-01') turns both into day numbers and gives 9 days. A NULL date gives NULL.

The hidden setup adds one more order for Ana on 2026-05-20, so she has three orders; only her first two count. The query must print:

name | first_order | second_order | days
Ana Pop | 2026-03-02 | 2026-04-05 | 34
Ben Ortiz | 2026-03-15 | 2026-05-21 | 67
Chloe Ng | 2026-04-18 | 2026-05-30 | 42
Dan Weber | 2026-05-07 | NULL | NULL
(4 rows)

Your task: the named query numbered already reads orders joined to customers. Give it name, ordered, a per-customer order_number and the next_order date. Then select the first date, the second date and the days between them, for order number 1 only.

Assignment
Follow the Coding Tutorial to build the numbered query and the main query that reads it, one clause per step.

Hint
Both window columns use the same OVER (PARTITION BY customer_id ORDER BY ordered). On row 1, next_order is the second order; Dan has only one order, so his stays NULL.