Returning Sum of all rows that fit date criteria

Question

I've really searched but couldn't find an answer to this one, I have a simple table in my postgres DB;

start_date | end_date | amount

Because the dates aren't continuous and because of the nature of needing a "snapshot date" I'm using a generate_series to create a separate table and attempting to join the two. What I need is that for every row where the date in my generate_series table falls between the start and end date in my table I will sum all of those rows and put that amount next to the date in my generate_series table.

I am not sure how I join the two tables and I feel I need to have some kind of loop that loops through all relevant rows and sums them. Ideally the solution would all be in SQL so that I can plug into Looker as a derived table without the need for pre ETLs.

Any help/thoughts would be greatly appreciated

Thanks

Answer 1

I hope I understood correctly that this is a join of tables. I include a sample of datum that is not in any range. Such has a sum of 0.

SQL:

select date, coalesce(sum(amount),0) as sum from 
(select dates.date as date, snap.amount as amount
 from dates left join snap
  on
   dates."date" > snap."start date"
   and dates."date" < snap."end date") as a
group by date
order by date
;

Input:

Table dates

date
2021-01-02T00:00:00Z
2021-01-09T00:00:00Z
2021-01-13T00:00:00Z
2021-01-20T00:00:00Z

Table snap

start date	end date	amount
2021-01-01T00:00:00Z	2021-01-10T00:00:00Z	2
2021-01-08T00:00:00Z	2021-01-15T00:00:00Z	5

Output:

date	sum
2021-01-02T00:00:00Z	2
2021-01-09T00:00:00Z	7
2021-01-13T00:00:00Z	5
2021-01-20T00:00:00Z	0

DDL:

CREATE TABLE snap
    ("start date" timestamp, "end date" timestamp, "amount" int)
;
    
INSERT INTO snap
    ("start date", "end date", "amount")
VALUES
    ('2021-01-01 00:00:00', '2021-01-10 00:00:00', 2),
    ('2021-01-08 00:00:00', '2021-01-15 00:00:00', 5)
;

CREATE TABLE dates
    ("date" timestamp)
;
    
INSERT INTO dates
    ("date")
VALUES
    ('2021-01-02 00:00:00'),
    ('2021-01-09 00:00:00'),
    ('2021-01-13 00:00:00'),
    ('2021-01-20 00:00:00')
;