Window functions

Start with the question

Use LAG for a previous value, an aggregate frame for a moving average, ROW_NUMBER for a newest-per-key choice, and RANK or DENSE_RANK when ties should share a rank. A window has three decisions: partition, order, and sometimes frame.

Missing PARTITION BY quietly compares one category with another. An unbroken tie in ROW_NUMBER can select a different winner without a data change. State the grain before writing the window, and use half-open time ranges on timestamp columns.

Your task

Create queries/weekly-category.sql. For every category and week in 2023, return line revenue, the prior week, week-over-week change, and a four-week moving average. Use CAST(date_trunc('week', ordered_at) AS DATE) and a half-open 2023 range.

Check your understanding

• Which question calls for LAG, which for a moving-average frame, and which for ROW_NUMBER?
• What does a missing PARTITION BY silently break?
• Why can ROW_NUMBER be the wrong choice for a tied top result?

Do it with your agent

Say next lesson, build the query by hand, then say review my work. Ask the agent to check the grain, boundary, partition, order, and moving-average frame.