Skip to content
Advertisement

SQL Windowing Aggregation Over Two Consecutive Date

I am not an SQL expert and finding this a bit challenging. Imagine I have the following table but with more users:

As you see they are grouped by user_id. Now what I like to do is add a column called sum_over_two_day which satisfies the following conditions:

  1. Grouped by user_id
  2. For each user it is grouped by the date
  3. The sum is then calculated per two consecutive calendar days for amount (today + previous calendar day)

So the output will be this:

Advertisement

Answer

The proper way is to use a window function with a RANGE clause:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement