Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Window Functions

What does OVER() do? What is PARTITION BY?

Mediumoverpartition bywindow

Interview question

What does OVER() do? What is PARTITION BY?

How to answer

The short answer

OVER() defines the window over which the calculation happens.

SQL
SUM(salary) OVER ()

calculates across all rows.

SQL
SUM(salary) OVER (
    PARTITION BY department
)

calculates separately for each department.

GROUP BY

PARTITION BY

It just splits the rows into groups for the window calculation. You still keep every 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