How to Write SQL Window Functions with AI

A grouped aggregate often reduces several input rows to one result per group. A window calculation can instead attach a group total, rank or neighboring value to each row. This makes it useful for reports that need both detail and context, such as each purchase alongside a customer's running spend. Give the AI the database engine, relevant columns and the expected output for a small dataset. Then define four choices. The partition identifies which rows belong together, such as all events for one account. The ordering determines their sequence. The function defines the calculation. For functions affected by a frame, the frame identifies which rows within the partition contribute to the current result. Ranking functions handle ties differently. ROW_NUMBER gives each row a distinct sequence number, but tied ordering values need a tie-breaker for a predictable assignment. RANK gives equal ranks to tied peers and leaves gaps afterward. DENSE_RANK gives equal ranks without those gaps. Choose based on the report's meaning. Running totals need particular care. An ordered window can have a default frame that includes peers with equal ordering values. For a total that advances one row at a time, specify a suitable ROWS frame and a deterministic order. Test tied timestamps rather than relying only on perfectly distinct sample values. LAG refers to an earlier row in the partition's ordering. It does not automatically fill missing calendar dates. A previous-row comparison can therefore differ from a previous-day comparison. PostgreSQL's window-function tutorial documents these distinctions. Ask the AI to explain its choices, execute the query on a small fixture, and compare every row with the expected ranking or total before applying it to a larger report.