How to calculate column (integer) changes in hourly buckets? I have following table:

id serial pk
created_at timestamp with time zone not null
user_id integer not null
points integer not null


For a given time period (min_date and max_date, both timestamp with time zone) it should return list of total (without partitioning by user_id) points changes within this period, partitioned by hour. Point change is a difference of points for a user since previous record for the same user.

For example:

created_at  | user_id | points
10:00       | 1       | 100
10:30       | 2       | 50
11:00       | 2       | 75
12:00       | 1       | 130
12:30       | 2       | 80
13:00       | 3       | 20


Without min/max_date it should return:

hour | points |
10   | 100 + 50 = 150
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35
13   | 20


With min/max_date it should return:

min_date=11:00
max_date=12:00

hour | points |
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35


So basically it should return sum of user point changes for each hour bucket since previous (outside of current bucket) row entry. 

Note if there are more than one rows for same user in same hour bucket, then the latest should be used in calculation. Also if there will be more than one rows for same user in same bucket hour with same created_at then maximum value of "points" for given user in given bucket should be used in calculation. 

Actually "points" for given user will ALWAYS grow (or will be the same as previous) so it is possible to just use max(points) for user in given bucket for calculation.

In examples above I simplified "created_at" field to just hour, but in reality it's a timestamp with time zone and it should be grouped by extract(year from created_at), extract(day from created_at), ....

So far I have:

with stats_buckets as (
  select 
    extract(year from stats.created_at) as year, 
    extract(month from stats.created_at) as month,
    extract(day from stats.created_at) as day,
    extract(hour from stats.created_at) as hour,
    max(stats.points), -- use max points value inside time-user bucket since points always grow (dont have to use latest entry for given user)
    stats.user_id
  from points as stats
  group by year, month, day, stats.user_id
)
select * from stats_buckets


It just groups records by year, month, day, hour, user_id and selects max(points) from each bucket. Now for each bucket I have to calculate difference between selected max(points) and previous points for given user outside of the bucket. And then group results by year, month, day, hour.

I tried to do this with windowing function but without any luck so far. 

Also one more important thing: there might be a LOT of rows in points table so calculating the differences for ALL of the buckets might be too slow. However min_date, max_date will ALWAYS be used to prevent calculating ALL buckets. The problem is that it's not possible to filter out point records initially by min/max_date, since for each row, the previous one (from which we calculate the difference) might be outside of min/max_date range.
This query is for all the buckets.

with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
  select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points 
)
, points_with_lag as ( -- Calcuate lag point by user_id
  select date_id, user_id, hour, points, 
    lag(points, 1, 0 ) over w points_before
  from points_enhanced pe
  window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
  select date_id, hour, user_id, max(points)  - min(points_before) points_bucket 
  from points_with_lag
  group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id


This query is for min_date / max_date filter.
Adjust the dates to your needs.

with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
  select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points 
  where created_at &gt;= '2017-05-30 12:00:00' and created_at &lt; '2017-05-30 14:00:00' -- filter by min_date/max_date
)
, points_with_lag as ( -- Calcuate lag point by user_id
  select date_id, user_id, hour, points, 
    lag(points, 1, 
                 ( select coalesce(max(points), 0) from public.points p -- This correlated subquery gets the lag point value, or 0, for that user_id before the min_date limit
                   where created_at &lt; '2017-05-30 12:00:00'
                   and p.user_id = pe.user_id
                 ) 
    ) over w points_before
  from points_enhanced pe
  window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
  select date_id, hour, user_id, max(points)  - min(points_before) points_bucket 
  from points_with_lag
  group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id


Regards.