SNOWFLAKE SQL how to find aggregate sum for each record based on date range but each record has different date range

So I have two tables that look like this:

Table_A

Table_B

I want to find the total sales for each product in Table_A between the start date and end date.

So by the end I have a table like this:

>Solution :

You’ll join these tables on the Product and whether the date_sold is in the date range in table_a or not, using the BETWEEN operator:

SELECT t1.Product, t1.start_date, t1.end_date, SUM(t2.quantity_sold) as total_sales
FROM table_a t1
   INNER JOIN table_b t2 
      ON t1.Product = t2.Product
      AND t2.date_sold BETWEEN t1.start_date and t1.end_date
GROUP BY t1.Product, t1.start_date, t1.end_date