Window functions answer "compared with what?" questions without losing rows. For each question decide three things before writing OVER (...):
- Which rows belong together? That is
PARTITION BY (per customer, per product), or nothing for the whole table.
- In what order? That's the
ORDER BY inside OVER.
- What to compute? A rank, a running sum, or the previous or next value.
Filtering on a window result needs a CTE or subquery around it. Ties matter for ranks: pick ROW_NUMBER, RANK or DENSE_RANK on purpose.