Input table:events GOAL I want max(distinct user) grouped by month column. my final result need two columns one is month and another one is max_count I need output similar to this I followed some approach Approach1: if i follow this approach, it is just giving me only max_count but i need month column as well I know we can use

sql: query to find max count with extra columns as well

Input table:events

month      user
2020-11   user_1
2020-11   user_5
2020-11   user_3
2020-12   user_2
2020-10   user_4
2020-09   user_6

​x
 
month      user2020-11   user_12020-11   user_52020-11   user_32020-12   user_22020-10   user_42020-09   user_6​​

GOAL

I want max(distinct user) grouped by month column.
my final result need two columns one is month and another one is max_count

I need output similar to this

month    max_count
2020-11    3

 
month    max_count2020-11    3​

I followed some approach

Approach1:

select max(cnt) max_count
from
(
select month,
       count(distinct user) as cnt
       
from events

group by 1
)

 
select max(cnt) max_countfrom(select month,       count(distinct user) as cnt       from events​group by 1)​​

if i follow this approach, it is just giving me only max_count but i need month column as well

I know we can use something like order by and limit to get the result. But i dont want that hacky way.

Can anyone suggest a solution for this?

Answer

Use a window function:

select month, cnt
from
(
  select month,
         count(distinct "user") as cnt,  
         dense_rank() over (order by count(distinct "user") desc) as rnk
  from events
  group by month
) t
where rnk = 1;

 
select month, cntfrom(  select month,         count(distinct "user") as cnt,           dense_rank() over (order by count(distinct "user") desc) as rnk  from events  group by month) twhere rnk = 1;​

user is a reserved keyword in SQL and should be quoted (or better: find a different name)

Advertisement

Answer