Introduction
ROWS
RANGE
13. RANGE explained
Default window frame
Summary

Instruction

Excellent! It's time to look at another type of window frame: RANGE.

The difference between ROWS and RANGE is that RANGE will take into account all rows that have the same value in the column which we order by. This might be helpful with dates. Consider the following problem: we want to calculate the running sum from all orders sorted by date. We could write something like this:

SELECT
  Id,
  PlacedDate,
  TotalPrice,
  SUM(TotalPrice) OVER(ORDER BY PlacedDate ASC ROWS UNBOUNDED PRECEDING) AS RunningSum
FROM SingleOrder;
Id PlacedDate TotalPrice RunningSum
4 2016-06-13 2659.63 2659.63
5 2016-06-13 602.03 3261.66
6 2016-06-13 3599.83 6861.49
7 2016-06-29 4402.04 11263.53
1 2016-07-10 3876.76 15140.29
2 2016-07-10 3949.21 19089.50
3 2016-07-18 2199.46 21288.96
10 2016-08-01 4973.43 26262.39
11 2016-08-05 3252.83 29515.22
12 2016-08-05 3796.42 33311.64
8 2016-08-21 4553.89 37865.53
9 2016-08-30 3575.55 41441.08

And it would work. But our boss might say "Hey, I don't really need to see how the running sum changed during single days. Just show the values at the end of the day. If there are multiple orders on a single day, add them together".

This can be implemented by changing ROWS to RANGE. Notice how we've highlighted the rows which now share the same running sum because they come from the same date.

SELECT
  Id,
  PlacedDate,
  TotalPrice,
  SUM(TotalPrice) OVER(ORDER BY PlacedDate ASC RANGE UNBOUNDED PRECEDING) AS RunningSum
FROM SingleOrder;
Id PlacedDate TotalPrice RunningSum
4 2016-06-13 2659.63 6861.49
5 2016-06-13 602.03 6861.49
6 2016-06-13 3599.83 6861.49
7 2016-06-29 4402.04 11263.53
1 2016-07-10 3876.76 19089.50
2 2016-07-10 3949.21 19089.50
3 2016-07-18 2199.46 21288.96
10 2016-08-01 4973.43 26262.39
11 2016-08-05 3252.83 33311.64
12 2016-08-05 3796.42 33311.64
8 2016-08-21 4553.89 37865.53
9 2016-08-30 3575.55 41441.08

Exercise

Modify the example so that it shows the average TotalPrice for single days for each row. Rename the column to AvgTotalPrice.

Stuck? Here's a hint!

You need to change both the aggregating function and the window frame.