Distinct count of one column based on the lack of frequency of another with unique values

user_18225840 0
Viewed 46

I have the following data in a table

Transaction date ID # Transaction ID #
Jan 1st,2021 9991 158423
Jan 1st,2021 9991 537912
Jan 1st,2021 9988 417823
Jan 1st,2021 9981 659231
Jan 1st,2021 9980 832239

I want to find out the distinct count of the ID #s only when there's only 1 transaction associated with that ID. The transaction ids do not repeat and are all unique as well.

I have this base code here for all distinct values

select
    date_trunc('year',transaction_date) as trx_month,
    count (distinct profile_id) as profile_count,
    count (fk_transaction_id) as trx_count,
    count (distinct fk_transaction_id)/count (distinct profile_id) as trx_per_customer
from my_table
group by 1
order by 1;

I would like the following output

Month Total profiles with only 1 trx
Jan 3
snowflake-cloud-data-platform if-statement sql
2 Answers

Using:

SELECT DISTINCT month,
       SUM(COUNT(*)) OVER(PARTITION BY month) AS cnt
FROM tab
GROUP BY month, ID
HAVING COUNT(DISTINCT transaction_id) = 1;

The trick here is to group by two columns month/id, use the HAVING to find only ID per month with one transaction.

Then the value is summed by month using windowed SUM.

db<>fiddle demo

user_50708790

Using vanilla SQL approach

with cte as 

(select month, id
 from tab
 group by month, id
 having count(distinct transaction_id) = 1)

select month, count(id) as single_tranx_id_count
from cte
group by month;
user_99471590
Related