Window Frames, LAG, NTILE and FIRST_VALUE

A reminder of the shape. function() OVER (PARTITION BY group ORDER BY order_col frame). PARTITION BY splits the rows into independent groups, ORDER BY orders the rows inside each group, and the optional frame says which of those ordered rows the function may see from the current row.

The frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means this row and the two rows before it: a three-row window, so AVG over it is a moving average. UNBOUNDED PRECEDING means from the first row of the partition, UNBOUNDED FOLLOWING means to the last.

SELECT day, amount,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg3
FROM daily_sales;

The default frame is a trap. With an ORDER BY and no frame, the window runs from the first row up to the current row (and includes rows tied with it). That makes SUM(x) OVER (ORDER BY day) a running total, which is what you want for totals but not for a moving average. For a fixed-size window, always write the frame. ROWS counts physical rows; RANGE counts all rows with the same ordering value.

LAG and LEAD navigate. LAG(col) reads the previous row's value inside the partition, LEAD(col) the next row's, and the first row of a partition has no previous row, so LAG gives NULL. Both accept an offset and a default: LAG(col, 2, 0).

SELECT customer_id, order_date,
       CAST(julianday(order_date) - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS INTEGER) AS days_since_prev
FROM orders;

FIRST_VALUE and LAST_VALUE. FIRST_VALUE(col) returns col from the first row of the frame; with the default frame that is the first row of the partition in your ORDER BY. LAST_VALUE sees only up to the current row by default, so it simply returns the current row: to get the true last value, widen the frame to UNBOUNDED FOLLOWING, or order the other way and use FIRST_VALUE.

SELECT id, customer_id,
       FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS latest_order
FROM orders;

NTILE(n) deals rows into n buckets. The buckets differ in size by at most one, and the extra rows go to the first buckets: 11 rows into 4 buckets gives sizes 3, 3, 3 and 2. It balances the number of rows, not the values, so two similar values can land in different buckets. Add a tie-breaker column to ORDER BY so the assignment is repeatable.

SELECT customer_id, total,
       NTILE(4) OVER (ORDER BY total DESC, customer_id) AS quartile
FROM customer_totals;

Window results cannot be filtered in WHERE, because windows are computed after WHERE runs. To keep rank 1 or the third bucket, put the query in a subquery or a CTE and filter in the outer query.