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
ID | MAX(TABLE2.DATE) | SUM(AMOUNT) |
---|---|---|
1 | 2022-03-01 | 30 |
2 | 2022-02-20 | 40 |
* Be the first to Make Comment