pbPassingBI
/
sql/flashcards/sql-cards-window

Window functions — flashcards

One card per lesson — 5 in this module. Recall the key points, then check. 5 cards. Click a card to flip, or use the arrow keys and space bar.

  1. Introduction to window functions — what are the key points?

    Window functions keep every row; GROUP BY collapses them · PARTITION BY defines the group the window is computed over · Windows are evaluated after WHERE, so filtering needs a wrapper Read the lesson

  2. PARTITION BY and ORDER BY — what are the key points?

    ORDER BY inside OVER changes the frame, turning an aggregate into a running total · ROWS counts rows; RANGE includes ties on the ORDER BY value · State ROWS explicitly for moving averages Read the lesson

  3. ROW_NUMBER, RANK and DENSE_RANK — what are the key points?

    RANK skips after ties, DENSE_RANK does not, ROW_NUMBER never ties · ROW_NUMBER guarantees exactly N rows for a top-N query · ROW_NUMBER with PARTITION BY is the standard deduplication pattern Read the lesson

  4. LAG and LEAD — what are the key points?

    LAG returns null on the first row unless given a default · Guard the denominator with NULLIF when computing percentage change · LAG moves by row, not by period — missing periods skew comparisons Read the lesson

  5. Running totals and moving averages — what are the key points?

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the running-total frame · PARTITION BY resets the cumulative sum per group · The first rows of a moving average cover fewer periods Read the lesson