Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in PostgreSQL

Introduction
Quick refresher
PARTITION BY ORDER BY with ranking
7. RANK() with PARTITION BY ORDER BY
PARTITION BY ORDER BY with window frames
PARTITION BY ORDER BY with analytical functions
Summary

Instruction

Ok! We'll introduce the features chronologically. In part 4, you learned ranking functions. They are one of the places where you can apply PARTITION BY and ORDER BY together.

So far, all the rankings we calculated were performed for all the rows from the query result. With that knowledge, we could have calculated the position of each store in the global network based on their ratings:

SELECT
  id,
  country,
  city,
  rating,
  RANK() OVER(ORDER BY rating DESC)
FROM store;
Diagram A

Now, we can add PARTITION BY to calculate the positions independently for each country:

SELECT
  id,
  country,
  city,
  rating,
  RANK() OVER(PARTITION BY country ORDER BY rating DESC)
FROM store;

In this way, we create a separate ranking for each country, so Paris and Frankfurt can both get rank = 1 for the separate rankings in France and Germany:

diagram

Exercise

Take into account the period between August 10 and August 14, 2016. For each row of sales, show the following information: store_id, day, number of customers and the rank based on the number of customers in the particular store (in descending order).

Stuck? Here's a hint!

Use PARTITION BY store_id ORDER BY customers DESC.