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.
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.