CODEWITHSUNDEEP
sqlpostgresqlfrequencytopmost
user3120266
user3120266

Reputation: 425

Find the most common varchar from a group of users using SQL

I have thousands of users and I am trying to find their most common segment. Data looks like this:

User               Segment
User 1               Good
User 1               Good
User 1               Poor
user 2               Medium
user 2               Medium
User 3               Poor

In this scenario, the sql code would return:

Good for User 1

Medium for User 2

Poor for User 3

I tried using a Max function but this only returns the string in alphabetic order and not the most common one associated with each user. As an fyi, the segment can change often between users. So its very likely it can change 8-10 times but looking for the one that represents them the most.

Thanks!

Upvotes: 0

Views: 94

Answers (3)

Rajat
Rajat

Reputation: 5803

Since window functions are applied after the group by you could also do as below. You can replace row_number() with rank() or dense_rank() function depending on how you want to deal with ties.

select user, segment 
from
(select user, segment, row_number() over (partition by user order by count(*) desc) as rn
from your_table 
group by user, segment) t
where rn=1

Upvotes: 1

django-unchained
django-unchained

Reputation: 844

You can use row_number() and count() analytic functions to do this:

select user,segment 
from (
  select user, segment, cnt,
    row_number() over(partition by user,segment order by cnt desc) as rn 
  from (
    select user, segment,
      count(segment) over(partition by user,segment) as cnt 
    from table) t1
  ) t2
where rn = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use aggregation, like this:

select user,
       coalesce(max(case when segment = 'Good' then segment end),
                max(case when segment = 'Medium' then segment end),
                max(case when segment = 'Poor' then segment end)
               )
from t
group by user;

Upvotes: 1

Related Questions