Which mistakes most often break a window function query?
Five mistakes, and every one of them is a query that used to work in a group by and then stopped working when a window function was added.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
placed_on TEXT NOT NULL
);
The first mistake is mixing a window function with a group by in the same query. When you group the rows and then use a window function, the window function sees the grouped rows and not the original ones. The number of rows that the window function works on is the number of groups, and that is almost never what you wanted.
The second mistake is forgetting the partition. The window then covers the whole table, and the calculation is wrong for every group except the one you were looking at.
The third mistake is using the wrong frame. The default frame for an ordered window includes every row that has the same order value as the current row, so two rows on the same day give the same running total, which is rarely what a running total means.
The fourth mistake is filtering on the window function. You cannot use its name in the where clause of the same query, because the filter runs before the window is calculated.
The fifth mistake is an order that is not a total order. If two rows have the same order value and there is nothing to break the tie, then the order between them is not decided, and the answer can be different every time you run the query.
The next question in this topic looks at the fourth mistake, because it is the one that stops the query from running at all.