Ranks and Trends With Window Functions

Goal

You use row_number, rank, dense_rank, running totals, lag, shares, and ntile to compute rankings and trends, and learn to judge when to use window functions.

Why it matters

Aggregation folds rows. After folding, the information about individual rows is gone. But many questions in practice can be answered only by referring to the surroundings while keeping individual rows, such as "which number is each row within its own group", "how does it compare with the previous value", and "what percentage of the total is it".

Window functions fill exactly this place. You can do the same thing with a self-join or a correlated subquery, but the code gets long and the table is read several times. A window function finishes in a single scan.

You only need to remember one constraint. The result of a window function cannot be used in the WHERE of the same SELECT. This is because WHERE comes first in the processing order. So to filter by rank, as in "only the top 3", you must wrap it one layer in a subquery or CTE.

Steps

Sort products by price descending within each category (ties broken by id ascending) and create a view containing the resulting sequence numbers, named v_ranked_products. The columns are category, id, price, rn.
Rank all products by price descending in two ways, in a view named v_rank_compare. The columns are id, price, rnk (skips the numbers after ties), and drnk (does not skip).
Create a view containing the monthly revenue and cumulative revenue of completed orders, named v_running_revenue. The columns are month, revenue, cum_revenue, and the month basis is AT TIME ZONE 'Asia/Seoul'.
Create a view, named v_mom, that adds the previous month's revenue and the change to the same monthly aggregation. The columns are month, revenue, prev_revenue, diff, and the prev_revenue of the first month must be empty.
Create a view containing revenue by channel and its share of the total, named v_channel_share. The columns are channel, revenue, share_pct; round the share to two decimal places, and it must sum to 100.
Create a view containing the top 3 products by price per category, named v_top3_per_category. The columns are category, id, price.
Split products into four equal groups by price ascending (ties by id ascending) in a view named v_price_quartile. The columns are id, price, quartile.
Create a view containing each customer's first order, named v_first_order. The columns are customer_id, order_id, ordered_at, and if the time is the same, the order with the smaller id is treated as the first order.

Notes

Basic form: 함수() OVER (PARTITION BY 그룹 ORDER BY 정렬) (function, group, sort)
If you leave the parentheses empty as in OVER (), the whole result becomes one window.
Common mistake 1: if you use a rank result in the WHERE of the same SELECT, an error occurs.
Common mistake 2: if there are ties in the sort key, the result can differ from run to run, so add a tie-break column.

Number products by price within each category

Sort products by price descending within each category (ties broken by id ascending) and create a view containing the resulting sequence numbers, named v_ranked_products. The columns are category, id, price, rn.

Use together the clause that splits the window and the clause that sets the order within the window. Break ties by id.

Compare two rank functions

Rank all products by price descending in two ways, in a view named v_rank_compare. The columns are id, price, rnk (skips the numbers after ties), and drnk (does not skip).

There is a function that skips the numbers after ties and a separate one that does not.

Compute cumulative revenue

Create a view containing the monthly revenue and cumulative revenue of completed orders, named v_running_revenue. The columns are month, revenue, cum_revenue, and the month basis is AT TIME ZONE 'Asia/Seoul'.

Build the monthly aggregation first and open the window on top of that result. If you reverse the order, aggregation and window get mixed up.

Compute month-over-month change

Create a view, named v_mom, that adds the previous month's revenue and the change to the same monthly aggregation. The columns are month, revenue, prev_revenue, diff, and the prev_revenue of the first month must be empty.

There is a function that fetches the value of the previous row. In the first month there is no value, so it must be empty.

Compute the share of the total

Create a view containing revenue by channel and its share of the total, named v_channel_share. The columns are channel, revenue, share_pct; round the share to two decimal places, and it must sum to 100.

If you leave the OVER parentheses empty, the whole result becomes one window. You do not need to run the query twice.

Pick the top 3 per group

Create a view containing the top 3 products by price per category, named v_top3_per_category. The columns are category, id, price.

The result of a window function cannot be used in the WHERE of the same SELECT. You must wrap it one layer.

Split into price quartiles

Split products into four equal groups by price ascending (ties by id ascending) in a view named v_price_quartile. The columns are id, price, quartile.

There is a function that splits rows into n equal groups and numbers them. Match the sort key exactly.

Find each customer's first order

Create a view containing each customer's first order, named v_first_order. The columns are customer_id, order_id, ordered_at, and if the time is the same, the order with the smaller id is treated as the first order.

Number the orders by time within each customer and keep only the first. Customers with no orders must not appear.