PARTITION BY

So far, our windows have always consisted of the whole table. This doesn't have to be the case.

Let's go back to our Olympics shot put data. The rankings we generated in the last lesson have a problem. It would make more sense to have separate rankings by sex. So, we want to modify our window so that each row only sees other rows of the same sex, instead of the whole table. For this, we can add PARTITION BY to our window definition.

SELECT
  rank() OVER (PARTITION BY athlete_sex ORDER BY distance_m DESC),
  *
FROM shot_puts
ORDER BY athlete_sex, 1;

Now there are 2 totally independent rankings, as the window is partitioned by athlete_sex. Each row's window only includes other rows where the value of athlete_sex matches its own. Without PARTITION BY, as in the previous lesson, the entire table is considered 1 big partition.

Revisiting an Old Problem

When we first learned about aggregation, we analyzed employee data to select the top salary at each company. However, we ran into a problem where we couldn't get any other info about the employee with the top salary, only their salary.

We ended up solving this later when we learned about subqueries. We accomplished this by joining in a second query after aggregating.

Here, we'll look at an even cleaner way to solve this using window functions. The key is that we can rank the rows in employees and use our new PARTITION BY trick.

SELECT
  rank() OVER (PARTITION BY company ORDER BY salary DESC),
  *
FROM employees;

After that query, we can do another that filters out the rows that aren't the top rank.

WITH ranked_employees AS (
  SELECT
    rank() OVER (PARTITION BY company ORDER BY salary DESC),
    *
  FROM employees
)
SELECT *
FROM ranked_employees
WHERE rank = 1;

The benefit here is that we get all the other column information about the employee having the top salary, and we don't have to do a JOIN, which is arguably more confusing than this approach.

Even More Practice

If you'd like, try going back and solving the Latest Tweets without a LATERAL join.

WITH joined_tweets AS (
  SELECT
    u.*,
    t.body AS recent_tweet,
    t.created_at AS tweet_created_at,
    row_number() OVER (PARTITION BY t.user_id ORDER BY t.created_at DESC) AS num
  FROM follows f
  JOIN tweeters u
    ON u.id = f.followed_id
  JOIN tweets t
    ON t.user_id = u.id
  WHERE f.follower_id = 15
)
SELECT *
FROM joined_tweets
WHERE num <= 2
ORDER BY tweet_created_at DESC;