CODEWITHSUNDEEP
sql
brightman1309
brightman1309

Reputation: 33

How to select most frequent item id per customer?

I'm trying to find the most frequently purchased item (represented by item_id) for each customer id (customer_id).

select distinct on customer_id, most_freq_item from (
select customer_id, item_id as most_freq_item, count(*) as _count
from my_table
group by customer_id, item_id)
order by customer_id, _count desc;

This resulted in the error:

"syntax error at or near "customer _id" after "select distinct on".

Upvotes: 1

Views: 400

Answers (2)

pncsoares
pncsoares

Reputation: 63

SELECT stat.*
  FROM (SELECT customer_id, 
               item_id                                      most_freq_item,
               COUNT(*)                                     cnt,
               ROW_NUMBER() OVER (PARTITION BY customer_id
                                  ORDER BY COUNT(*) DESC)   seqnum
         FROM my_table
        GROUP BY customer_id,
               item_id) stat
 WHERE seqnum = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use window functions:

select ci.*
from (select customer_id, item_id as most_freq_item,
             count(*) as cnt,
             row_number() over (partition by customer_id order by count(*) desc) as seqnum
      from my_table
      group by customer_id, item_id
     ) ci
where seqnum = 1;

In the event of ties, this returns an arbitrary most frequent value. If you want all of them, use rank() instead of row_number().

Upvotes: 1

Related Questions