Introduction
PARTITION BY
6. Using PARTITION BY
Summary

Instruction

As you can see, the query works just fine. Imagine writing the same query using regular GROUP BY: you'd have to use a correlated subquery and a INNER JOIN. The query would be neither readable nor efficient.

We no longer want to pay that price and PARTITION BY is the solution. Thanks to PARTITION BY, we can easily get the information about individual rows AND the information about the groups these rows belong to.

Exercise

Show the Id of each journey, its Date, and the number of journeys that took place on that date (CountJourneys).

Stuck? Here's a hint!

Use COUNT(Id) OVER(PARTITION BY Date).