Sum all unique values from previous dates Let's say, for simplicity sake, I have the following table:
id      amount     p_id     date
------------------------------------------------
1       5          1        2020-01-01T01:00:00
2       10         1        2020-01-01T01:10:00 
3       15         2        2020-01-01T01:20:00 
4       10         3        2020-01-01T03:30:00 
5       10         4        2020-01-01T03:50:00 
6       20         1        2020-01-01T03:40:00

Here's a sample response I want:
{
 &quot;2020-01-01T01:00:00&quot;: 25, -- this is from adding records with ids: 2 and 3
 &quot;2020-01-01T03:00:00&quot;: 55  -- this is from adding records with ids: 3,4,5 and 6
}

I want to get the total (sum(amount)) of all unique p_id's grouped by the hour.
The row chosen per p_id is the one with the latest date. So for example, the first value in the response above doesn't include id 1 because the record with id 2 has the same p_id and the date on that row is later.
The one tricky thing is I want to include the summation of all the amount per p_id if their date is before the hour presented. So for example, in the second value of the response (with key &quot;2020-01-01T03:00:00&quot;), even though id 3 has a timestamp in a different hour, it's the latest for that p_id 2 and therefore gets included in the sum for &quot;2020-01-01T03:00:00&quot;. But the row with id 6 overrides id 2 with the same p_id 1.
In other words: always take the latest amount for each p_id so far, and compute the sum for every distinct hour found in the table.
Look for
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY date DESC) rn
              FROM source_table
              WHERE date_trunc('hour', date) = @needed_datetime_truncated_to_hour )
SELECT @needed_datetime_truncated_to_hour, SUM(amount)
FROM cte
WHERE rn = 1;