I have 2 tables as follows:

Table1:

ID  Date  

1   2022-01-01
2   2022-02-01
3   2022-02-05

Table2

ID   Date         Amount
 
1    2021-08-01     15
1    2022-02-10     15
2    2022-02-15      20
2    2021-01-01     15
2    2022-02-20     20
1    2022-03-01     15

I want to select the rows in Table2 such that only rows past the Date in Table1 are selected in Table2 and calculate a sum of amounts of each subset and max(date) in Table2 for each subset grouped by ID. So the result would look like

ID    Date         Amount
1     2022-03-01    30
2     2022-02-20    40

SQL newbie here...I tried an inner join, but wasnt able to pass the date filter along...

Tried query:

with table1 as (select * from table1)
,table2 as (select * from table2)
select * from table1 a
inner join table2 b on (a.id=b.id)

Thanks!

CodePudding user response：

Not personally familiiar with Snowflake but a standard SQL query that should work would be:

select id, Max(date) Date, Sum(Amount) Amount
from Table2 t2
where exists (
  select * from Table1 t1 
  where t1.Id = t2.Id and t1.Date < t2.Date
)
group by Id;

CodePudding user response：

Here is how I would do this with Snowflake:

--create the tables and load data

--table1
CREATE TABLE TABLE1 (ID NUMBER, DATE DATE);

INSERT INTO TABLE1 VALUES (1,   '2022-01-01');
INSERT INTO TABLE1 VALUES (2  , '2022-02-01');
INSERT INTO TABLE1 VALUES (3  , '2022-02-05');

--table 2
CREATE TABLE TABLE2 (ID NUMBER, DATE DATE, AMOUNT NUMBER);
 
INSERT INTO TABLE2 VALUES(1,   '2021-08-01',    15);
INSERT INTO TABLE2 VALUES(1,   '2022-02-10',    15);
INSERT INTO TABLE2 VALUES(2,   '2022-02-15',    20);
INSERT INTO TABLE2 VALUES(2,   '2021-01-01',    15);
INSERT INTO TABLE2 VALUES(2,   '2022-02-20',    20);
INSERT INTO TABLE2 VALUES(1,   '2022-03-01',    15);

Now obtain the data using a select

SELECT TABLE1.ID, MAX(TABLE2.DATE), SUM(AMOUNT)
FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID
  AND TABLE1.DATE < TABLE2.DATE 
  GROUP BY TABLE1.ID

Results