I am doing some SQL exercises on a postgreSQL db and have stumped myself. Here are the tables involved:
Here is what I came up with, but its not the results I'm looking for:
The problem with the above is that its displaying the count per category of ALL the categories for each customer.
SELECT distinct(customerid),max(cat_count),cat_name AS top_count FROM(
SELECT customerid,count(c.categoryname) AS cat_count,c.categoryname AS cat_name
FROM cust_hist AS ch
JOIN products AS p
ON ch.prod_id = p.prod_id
JOIN categories AS c
ON p.category = c.category
GROUP BY customerid,c.categoryname
ORDER BY customerid DESC
)AS subA GROUP BY customerid,cat_count,cat_name ORDER BY customerid
What I would like to do is see what category each customer has purchased the most.
I'd like to see customerid, the count of items in the most purchased category, and the category name. Thanks in advance.