I have a table that looks like this:
store_id cust_id amount indicator
1 1000 2.05 A
1 1000 3.10 A
1 2000 3.10 A
2 1000 5.10 B
2 2000 6.00 B
2 1000 1.05 A
What I'm trying to do is find the percent of sales with indicators A, B for each store by only looking at unique customer IDs (i.e., the two sales to customer 1000 at store 1 would only count once). Something like this:
store_id pct_sales_A pct_sales_B pct_sales_AB
1 1.0 0.00 0.00
2 0.0 0.50 0.50
I know that I can use a subquery to find the counts of each transaction type, but I'm having trouble only counting the distinct customer IDs. Here's an (incorrect) approach for the pct_sales_A column:
SELECT
store_id,
COUNT(DISTINCT(CASE WHEN txns_A>0 AND txns_B=0 THEN cust_ID ELSE NULL))/COUNT(*) AS pct_sales_A --this is wrong
FROM (SELECT store_id, cust_id,
COUNT(CASE WHEN indicator='A' THEN amount ELSE 0 END) as txns_A,
COUNT(CASE WHEN indicator='B' THEN amount ELSE 0 END) as txns_B
FROM t1
GROUP BY store_id, cust_id
)
GROUP BY store_id;
You can use conditional aggregation with COUNT(DISTINCT):
SELECT store_id,
COUNT(DISTINCT CASE WHEN indicator = 'A' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a,
COUNT(DISTINCT CASE WHEN indicator = 'B' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a,
FROM t1
GROUP BY store_id;
Based on your comment, you need two levels of aggregation:
SELECT store_id,
AVG(has_a) as ratio_a,
AVG(has_b) as ratio_b,
AVG(has_a * has_b) as ratio_ab
FROM (SELECT store_id, cust_id,
MAX(CASE WHEN indicator = 'A' THEN 1.0 ELSE 0 END) as has_a,
MAX(CASE WHEN indicator = 'B' THEN 1.0 ELSE 0 END) as has_b
FROM t1
GROUP BY store_id, cust_id
) sc
GROUP BY store_id;
I think you want two levels of conditional aggregation:
select
store_id,
avg(has_a = 1 and has_b = 0) pct_sales_a,
avg(has_a = 0 and has_b = 1) pct_sales_b,
avg(has_a + has_b = 2) pct_sales_ab
from (
select
store_id,
cust_id,
max(indicator = 'A') has_a,
max(indicator = 'B') has_b
from t1
group by store_id, cust_id
) t
group by store_id
store_id | pct_sales_a | pct_sales_b | pct_sales_ab
-------: | ----------: | ----------: | -----------:
1 | 1.0000 | 0.0000 | 0.0000
2 | 0.0000 | 0.5000 | 0.5000