Practice Test GeeksSQL Practice Test
← All SQL Flashcard Decks

Advanced Window Functions Flashcards

7 cards from real SQL practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 Advanced Window Functions flashcards as text

  1. What is the default window frame when ORDER BY is specified but no frame clause is given?

    Answer: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    The SQL default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  2. How do ROWS and RANGE frame modes differ when there are duplicate ORDER BY values?

    Answer: ROWS counts physical rows; RANGE groups peers with equal values

    ROWS treats each row individually while RANGE includes all peer rows sharing the ordering value.

  3. What does LEAD(value, 1, 0) return when no following row exists?

    Answer: 0

    The third argument supplies a default (0 here) when the offset row does not exist.

  4. Which window function returns a value between 0 and 1 representing relative standing?

    Answer: PERCENT_RANK()

    PERCENT_RANK() returns a relative rank as a fraction from 0 to 1.

  5. In ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, how many rows are in the frame at most?

    Answer: 3

    The frame spans the previous, current, and next rows for a maximum of three.

  6. Why might you use a window function instead of GROUP BY for aggregates?

    Answer: To keep individual rows while adding aggregate columns

    Window functions add aggregate results without collapsing the detail rows.

  7. What does CUME_DIST() compute?

    Answer: The cumulative distribution: fraction of rows at or below the current value

    CUME_DIST() returns the proportion of rows with a value less than or equal to the current row.