I have a table – ‘clicks_plus_noClicks_raw’ like below
EmailAddress sentdate clickdate pid a@gmail.com 15 August 2021 23:30 4000 b@gmail.com 15 August 2021 23:30 5000 b@gmail.com 15 August 2021 23:30 5000 b@gmail.com 15 August 2021 23:30 5000 b@gmail.com 16 August 2021 23:30 5000 c@gmail.com 16 August 2021 23:30 17 August 2021 23:30 5000 b@gmail.com 16 August 2021 23:30 8000 Expected results EmailAddress pid count b@gmail.com 5000 0
If customer has not clicked on any of the last 3 emails we sent(exact 3), I want to add the result to final table with count as 0.
a@gmail.com & b@gmail.com should be skipped because we dint send them 3 emails yet.The final results should be only for ones we sent 3 emails.
I have this query, but the query counts even last 1 send, which is not what I want.
select t1.EmailAddress, t1.pid,count(case when rn <= 3 then t1.clickdate else null end) as Clickcount from ( select t1.EmailAddress, t1.pid, row_number() over(partition by t1.EmailAddress, t1.pid order by t1.sentdate desc) rn, t1.clickdate from clicks_plus_noClicks_raw t1 ) t1 group by t1.EmailAddress,t1.pid
Advertisement
Answer
You need a HAVING
clause in your query to check for the number of emails and a WHERE
clause to return only the last 3 emails:
SELECT EmailAddress, pid, COUNT(clickdate) count FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY EmailAddress, pid ORDER BY sentdate DESC) rn FROM clicks_plus_noClicks_raw ) t WHERE rn <= 3 GROUP BY EmailAddress, pid HAVING COUNT(*) >= 3;
See the demo.