Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Window Functions

ROWS vs RANGE

Mediumrowsrangeframe

Interview question

What is the difference between ROWS and RANGE in a window frame?

How to answer

The short answer

ROWS works with physical rows.

RANGE uses values in the ORDER BY expression, so peer rows with the same ordering value can land in the same frame.

For example:

SQL
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

means:

> Current row + previous two physical rows.

Handy for a rolling 3-row calculation.

Example:

SQL
AVG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill