I have a table like the following:

I want to relatively count how often a certain filter was used by all users.

The expected result would be:

I know I could do something like:

select count(*) / max(total)
from mytable
join (select count(distinct SearchID) total from mytable) on 1=1
group by Filter

but that is not very efficient. My guess is that I could do something with count(*) over (partition by ...) but I am not sure how exactly.

Any help would be appreciated.

P.S.: I am using snowflake but I could easily change that if necessary.

CodePudding user response：

You can do:

with s as (select filter, count(*) as cnt from t group by filter)
select filter, cnt / max(cnt) over() as ratio from s

CodePudding user response：

I would assign the total counts to a variable and use that as a denominator

set all_search_ids=(select count(distinct search_id) from mytable);

select filter, 
       count(distinct search_id)/$all_search_ids
from mytable
group by filter;

But if you really want to use windows functions, here's one way

select distinct 
       filter, 
       count(distinct search_id) over (partition by filter)/count(distinct search_id) over()
from mytable;

Note: The reason I didn't use the max(total) is because it would only work if a given filter was used in all the search queries