Introduction
PARTITION BY ORDER BY with ranking
PARTITION BY ORDER BY with window frames
PARTITION BY ORDER BY with analytical functions
Instruction
Now, let's talk about the use of analytical functions with PARTITION BY ORDER BY. Take a look at the following example:
SELECT country, city, opening_day, LEAD(city, 1, 'NaN') OVER(PARTITION BY country ORDER BY opening_day) FROM store;
In the above example, we show the country, city and opening_day of each store, but we also show the city where the next store was opened – in the same country, of course.
Exercise
For each store, show the sales in the period between August 5, 2016 and August 10, 2016: store_id, day, number of transactions, number of transactions on the previous day and the difference between these two values.
Stuck? Here's a hint!
Use LAG(transactions).
