I have following 2 tables: Sales and Menu. SALES TABLE
| customer_id | product_id |
|---|---|
| A | 1 |
| A | 2 |
| A | 2 |
| A | 3 |
| A | 3 |
| A | 3 |
| B | 1 |
| B | 1 |
| B | 3 |
| B | 3 |
| B | 2 |
| B | 2 |
| C | 3 |
| C | 3 |
| C | 3 |
MENU TABLE
| product_id | product_name |
|---|---|
| 1 | sushi |
| 2 | curry |
| 3 | ramen |
**My problem statement is : ** Which item was the most popular for each customer.
So I am expecting the following output.
| customer_id | product_name |
|---|---|
| A | ramen |
| B | sushi |
| C | ramen |
I have tried the following query :
SELECT s.customer_id, m.product_name,
(SELECT count(s.product_id) as freq
from sales
GROUP BY product_id
ORDER BY freq DESC
LIMIT 1) as maxFreq
from sales s
INNER JOIN menu m
ON s.product_id = m.product_id
GROUP BY s.customer_id, s.product_id;
I can not apply group by on maxfreq because it is an inner query. Otherwise my work would be done.
With MySQL supported version (v8 and above), you can try this:
SELECT customer_id, product_name
FROM
(SELECT customer_id, product_name,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY COUNT(product_name) DESC) AS Rn
FROM Sales s JOIN Menu m ON s.product_id=m.product_id
GROUP BY customer_id, product_name) v
WHERE Rn=1;
By using ROW_NUMBER() order by count descending of menu appearing for each customer.
Here's an alternative solution for older MySQL version:
SELECT customer_id,
SUBSTRING_INDEX(GROUP_CONCAT(product_name ORDER BY f DESC, product_id),',',1) AS product_name
FROM
(SELECT customer_id, product_name, COUNT(product_name) AS f, m.product_id
FROM Sales s JOIN Menu m ON s.product_id=m.product_id
GROUP BY customer_id, product_name, m.product_id) v
GROUP BY customer_id;
The count is done in the subquery then using GROUP_CONCAT() with order by count descending and product_id ascending for similar menu with similar count. After that, using SUBSTRING_INDEX() to return the first value from the GROUP_CONCAT() identifying the comma as delimiter.