Unique customers who received 1, 2, or emails

January 10, 2024

I’m trying to find the count of unique customers who received 1/2/3 emails from the table below:

cust_id	month	email_cnt
1	202311	1
1	202311	1
1	202310	1
1	202310	0
1	202309	1
2	202311	0
2	202311	0
2	202310	0
2	202310	1
2	202309	1
3	202311	0
3	202311	1
3	202310	0
3	202310	0
3	202309	1

This is the desired output, after summing the # of emails per month, count the number of unique customers who received x number of emails:

month	cust_with_1email	cust_with_2email
202311	2	1
202310	2	0
202309	3	0

I’m trying to use a CASE function without success:

SELECT distinct month, 
SUM(case when email_cnt = 1 then email_cnt end) cust_with_1email, 
SUM(case when email_cnt = 2 then email_cnt end) cust_with_2email, 
SUM(case when email_cnt = 3 then email_cnt end) cust_with_3email
FROM mytable
GROUP BY month

>Solution :

i can’t still not reproduce your result.

But

WITH CTE as (SELECT
cust_id, month, SUM(email_cnt) email_cnt
FROM mytable
GROUP BY cust_id, month)
SELECT month, 
SUM(case when email_cnt = 1 then email_cnt ELSE 0 end) cust_with_1email, 
SUM(case when email_cnt = 2 then email_cnt ELSE 0  end) cust_with_2email, 
SUM(case when email_cnt = 3 then email_cnt ELSE 0  end) cust_with_3email
FROM CTE
GROUP BY month