Provide an example business question that can only be answered in SQL using a window function, and not through standard GROUP BY aggregation.

The business question that can only be answered in SQL using a window function, and not through standard GROUP BY aggregation, is: "For every individual sales transaction, retrieve its transaction ID, the customer ID, the transaction amount, and the average transaction amount for that specific customer across all their transactions."

A standard GROUP BY aggregation, such as `SELECT customer_id, AVG(transaction_amount) FROM sales_transactions GROUP BY customer_id;`, would return a single row for each unique customer, showing only their average transaction amount. This approach aggregates, or collapses, all individual transaction details for a customer into one summary row, thereby losing the visibility of each unique transaction ID and its specific amount. The problem asks to see the individual transaction details *alongsidethe customer's overall average. This is where standard GROUP BY fails because it fundamentally alters the row structure, combining multiple rows into one and discarding the original individual row details unless those details are themselves part of the grouping key, which would lead to a group for each transaction, nullifying the desired aggregation across all customer transactions.

A window function addresses this by performing calculations across a set of related rows without collapsing the original rows. A window function computes a value for each row in the result set, much like a regular column, but its calculation can involve data from other rows within a defined "window". The "window" refers to the specific set of rows related to the current row on which the function operates. The `OVER` clause is crucial as it defines this window for the window function. Within the `OVER` clause, `PARTITION BY` divides the result set into logical segments or groups, known as partitions, and the window function is applied independently within each partition. For the given business question, `PARTITION BY customer_id` would group all transactions by a specific customer, allowing the average transaction amount to be calculated for each customer's transactions separately. The `ORDER BY` clause within the `OVER` clause defines the logical order of rows within each partition; while it defines processing order and is essential for functions like running totals or ranking, for a simple aggregate like an average calculated over an entire partition, it does not change the resulting aggregate value. By using a window function like `AVG(transaction_amount) OVER (PARTITION BY customer_id)`, the average is computed for each customer's partition, and this calculated average is then attached to *every original transaction rowbelonging to that customer. This enables SQL to return all individual transaction details (transaction ID, transaction amount) while simultaneously displaying the customer's overall average transaction amount on each of their transaction rows, a result unattainable with standard GROUP BY aggregation alone because standard GROUP BY would force you to either lose individual transaction detail or group by individual transaction, which doesn't provide the customer's overall average for each row.