Advanced SQL Concepts: Window Functions — OVER() and PARTITION BY()

In the previous article, we learned how to use OVER().If you want to learn more about the simplest window function, click the related article below.

Related Article: Advanced SQL Concepts: Window Functions —OVER()

In this part, we’ll learn one construction that can be put in OVER(), namely PARTITION BY. The basic syntax looks like this:

 OVER (PARTITION BY column1, column2 ... column_n)

PARTITION BY works in a similar way as GROUP BY.Unlike GROUP BY, PARTITION BY does not collapse rows.

PARTITION BY easily computes the statistics for the whole group but keeps details about individual rows.

Example: Find the count of released films for each date separately.

SELECT
TITLE,
RELEASE_DATE,
COUNT(ID) OVER(PARTITION BY RELEASE_DATE) AS TOTAL_FILM_RELEASED
FROM MOVIE
ORDER BY 3 DESC

You see the total film released for each date.

Example: Find the count of films for each date, the budget should not be 0.

SELECT
TITLE,
RELEASE_DATE,
BUDGET,
COUNT(ID) OVER(PARTITION BY RELEASE_DATE) AS TOTAL_FILM_RELEASED
FROM MOVIE
WHERE BUDGET <> 0
ORDER BY 3 DESC;