SQL query to calculate sum value in SQL using 2 tables without using a join

Question

I have 2 tables in my database which have time series data. table 1 has the following schema

using SQL I'm creating a 3rd table using values from 1st and 2nd table. the 3rd table will have 3 columns

key . date_b, sum

. The row count in 3rd table should be same as in 2nd table and sum column for each key will be calculated using the logic, when a_date>b_date sum the count value, otherwise keep it 0.

There are 3 dates greater than 1st feb in table 1 so the sum is 9. while 2 dates greater than 2nd feb in table 1 so sum is 7 and so on.

in excel i used sumifs and was able to calculate it successfully but in SQL when i'm not able to do it, if i use joins multiple records are created since keys are duplicate. what will be the sql query for creating the table 3?

Sample query that I have used:

SELECT *,
CASE WHEN "date_a"> "date_b" 
    THEN SUM("count")OVER(PARTITION BY "key_b","date_b" ) 
    ELSE 0 END AS "sum"
from
(
SELECT *
  FROM table 1
) A
right JOIN
(
     SELECT * 
     FROM table_b
     
 ) B
ON A."key" = B."key")
C

Answer 1

You can try to use OUTER JOIN with condition aggregate with group by

Query #1

SELECT t2.`Key`,
      t2.b_date,
      SUM(CASE WHEN t1.a_date >  t2.b_date THEN t1.`count` ELSE 0 END) 'sum'
FROM Table2 t2
LEFT JOIN Table1 t1 
ON t2.`Key` = t1.`Key` 
GROUP BY t2.`Key`,
      t2.b_date;

Key	b_date	sum
1	2022-02-01	9
1	2022-02-02	7
1	2022-02-03	3
1	2022-02-04	0
1	2022-02-05	0
1	2022-02-06	0
1	2022-02-07	0
1	2022-02-08	0
1	2022-02-09	0
2	2022-02-09	0
3	2022-02-09	0
4	2022-02-09	0

---

View on DB Fiddle