Skip to content
Advertisement

Count Distinct Window Function with Groupby

I have a table that contains a user’s name, market, and purchase_id. I’m trying to use a window function in SnowSql without a subquery to count the distinct number of purchases a user bought as well as the total number of unique purchases for the market.

Initial Table

User Market Purchase_ID
John Smith NYC 1
John Smith NYC 2
Bob Miller NYC 2
Bob Miller NYC 4
Tim Wilson NYC 3

The desired result would look like this:

User Purchases Unique Market Purchases
John Smith 2 4
Bob Miller 2 4
Tim Wilson 1 4

The query I’ve been attempting without a subquery looks like the below but receives an error with the groupby.

Appreciate any assistance with this. Thanks!

Advertisement

Answer

This might work , you could wrangle to get into the format you’re after but it produces the answer without subquery.

Uses the awesome GROUPING SETS which allows multiple group-by clauses in a single statement – the exact error you were hitting :-).

Awesome question!

enter image description here

Copy|Paste|Run

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement