CODEWITHSUNDEEP
sqlteradata
Maria Nazari
Maria Nazari

Reputation: 690

Finding Rolling Sum SQL

I have this table with fields (campaign_launch_date, email_address_id). Emails are sent to users to entice them to buy. I want to figure out if any of the email_addresses bought anything (and the order total) within 7 days after the campaign_launch_date.

Tables that I have:
Table 1: (campaign_launch_date, email_address_id, launch_id)
Table 2: (email_address_id, user_id)
Table 3: (user_id, sales_transaction_date, order_amount)

This is what I have so far:

SELECT    t1.launch_id, 
          t3.user_id, 
          Sum(order_amount), 
          Count(order_amount)
FROM      t1 
JOIN      t2 
ON        t1.email_address_id = t2.email_address_id 
LEFT JOIN 
ON        t3.user_id = t2.user_id 
AND       t1.campaign_launch_date <= (t3.sales_transaction_date + interval '7' day) 
GROUP BY  1, 
          2

Upvotes: 1

Views: 57

Answers (3)

LukStorms
LukStorms

Reputation: 29667

Something like this?

select t1.launch_id, t2.user_id, 
sum(t3.order_amount) as order_amount, 
count(t3.order_amount) as total_orders
from Table1 as t1 
join Table2 as t2 on t2.email_address_id = t1.email_address_id
left join Table3 as t3 on (t3.user_id = t2.user_id and t3.sales_transaction_date between t1.campaign_launch_date and (t1.campaign_launch_date + INTERVAL '7' DAY))
group by t1.launch_id, t2.user_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270493

I think your date logic is off. If I understand correctly:

select t1.launch_id, t3.user_id, sum(order_amount), count(order_amount)
from t1 join
     t2
     on t1.email_address_id = t2.email_address_id left join
     t3
     on t3.user_id = t2.user_id and
        t3.sales_transaction_date >= t1.campaign_launch_date  and
        t3.sales_transaction_date <= t1.campaign_launch_date + INTERVAL '7' DAY)
group by 1, 2;

Upvotes: 1

Danial Wayne
Danial Wayne

Reputation: 348

SELECT    t1.launch_id, 
          t3.user_id, 
          Sum(order_amount), 
          Count(order_amount)
FROM      t1 
JOIN      t2 
ON        t1.email_address_id = t2.email_address_id 
LEFT JOIN t3
ON        t2.user_id = t3.user_id 
WHERE     t1.campaign_launch_date <= (t3.sales_transaction_date + interval '7' day) 
GROUP BY  1, 
          2

Upvotes: 1

Related Questions