SQL query to consider exactly last 3 email send count and then return the respective click count

I have a table – ‘clicks_plus_noClicks_raw’ like below

EmailAddress  sentdate               clickdate               pid
[e-mail]   15 August 2021 23:30                            4000
[e-mail]   15 August 2021 23:30                            5000
[e-mail]   15 August 2021 23:30                            5000
[e-mail]   15 August 2021 23:30                            5000
[e-mail]   16 August 2021 23:30                            5000
[e-mail]   16 August 2021 23:30   17 August 2021 23:30     5000
[e-mail]   16 August 2021 23:30                            8000



Expected results
 EmailAddress            pid          count
 [e-mail]             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.

[e-mail] & [e-mail] 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 

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.