How to count number of distinct pairs per partition in SQL?

Question

I want to get the # of distinct pair combinations per level in my table.

Sample Data:

level       group_no    item_no
oiegown     1           1
oiegown     1           2
oiegown     1           2
oiegown     1           3
oiegown     2           1
wefwefw     1           1
wefwefw     2           2

My Attempt:

Wanted to do something like

COUNT(DISTINCT group_no, item_no) 
  OVER (PARTITION BY level)
AS item_count

Expected Output:

level       item_count
oiegown     4           
wefwefw     2           

But seems that COUNT() only accepts one argument in BigQuery. How can I modify my query to get my desired result?

Answer 1

You can join both column into one Text, and count the concatenated text

COUNT(DISTINCT CONCAT(group_no, item_no)) 
  OVER (PARTITION BY level)
AS item_count