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.

SELECT 
  user,
  COUNT(DISTINCT purchase_id),
  COUNT(DISTINCT purchase_id) OVER (partition by market)
FROM table
GROUP BY 1

Appreciate any assistance with this. Thanks!

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!

  SELECT 
      COUNT(DISTINCT PURCHASE_ID)  
    , USER_NAME
    , MARKET
 FROM 
    CTE
  GROUP BY 
    GROUPING SETS (USER_NAME, MARKET);

Copy|Paste|Run

WITH CTE AS (SELECT 'JOHN SMITH' USER_NAME, 'NYC' MARKET,   1 
PURCHASE_ID
UNION SELECT 'JOHN SMITH' USER_NAME,    'NYC' MARKET,   2 PURCHASE_ID
UNION SELECT 'BOB MILLER' USER_NAME,    'NYC' MARKET,   2 PURCHASE_ID
UNION SELECT 'BOB MILLER' USER_NAME,    'NYC' MARKET,   4 PURCHASE_ID
UNION SELECT 'TIM WILSON' USER_NAME,    'NYC' MARKET,   3 PURCHASE_ID) 

SELECT 
      COUNT(DISTINCT PURCHASE_ID)  
    , USER_NAME
    , MARKET
FROM 
    CTE
GROUP BY 
    GROUPING SETS (USER_NAME, MARKET);