Orders Gap Analysis

Prompt

Given the orders table below, write a query that returns one row per order, with an extra column showing how many days elapsed since that customer's previous order.

• The column must be named gap_days.
• The first order for each customer has no previous order: gap_days must be NULL (not 0).
• Orders are compared per customer — a customer's order is only compared with that same customer's earlier orders.
• If two orders share the same date, the gap between them is 0 days.
• orders.order_date may be NULL; such an order has an unknown date and its gap must be NULL.
• The output must include at least: customer_id, order_date, and gap_days.

In SQLite, the number of days between two dates is CAST(julianday(later) - julianday(earlier) AS INTEGER).

Example

Expected result (row order doesn't matter):

Constraints

• orders.id is the primary key.
• orders.order_date is nullable.

Tips

• LAG() is the natural tool here; it produces NULL for the first row in each partition.
• The order used by LAG must be deterministic — order by order_date, then by id as a tiebreaker.
• A NULL order date in the ORDER BY sorts last in SQLite, which is exactly the "unknown" behavior we want.

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: gap in days since the customer's previous order, NULL when first/unknown.
WITH ordered AS (
    SELECT id, customer_id, order_date,
           LAG(order_date) OVER (
               PARTITION BY customer_id
               ORDER BY order_date, id
           ) AS prev_order_date
    FROM orders
)
SELECT id, customer_id, order_date,
       CAST(julianday(order_date) - julianday(prev_order_date) AS INTEGER) AS gap_days
FROM ordered
ORDER BY id;