How to find the number of unique_ids based on the number of items purchased

Question

I asked a similar question on another thread.

How to find the number of types of buyers for each product per user_id?

I'm working on MySQL workbench 8.0. In this case, I want to find the number of unique user_ids who purchased a product once, twice, more than once, more than twice, etc.

The sample data similar to the original one is shown below.

user_id	Product
2	chair
1	chair
3	chair
4	chair
1	chair
2	chair
4	table
3	table
4	table
3	table
1	table
2	table
5	table

The number I want to find is the count of unique_ids. And I would like to find the following result:

Product	BoughtOnce	BoughtTwice
chair	4	2
table	5	2

For instance, 4 unique people bought only one chair and 2 unique people bought two chairs. Similarly, 5 unique people bought only one table and 2 unique people bought two tables.

From the other thread, I could give row numbers partitioned by user_id and product.

SELECT 
user_id, Product, ROw_NUMBER() OVER(PARTITION BY `user_id`, Product) rn
FROM myData;

Result:

user_id	Product	rn
1	chair	1
1	chair	2
1	table	3
2	chair	1
2	chair	2
2	table	3
3	chair	1
3	table	2
3	table	3
4	chair	1
4	table	2
4	table	3
5	table	1

How do I proceed to find the count of unique_ids based on the number of items purchased?

Let me know if you have another approach too.

I'd appreciate your help.

Answer 1

You could do this with two levels of aggregation: first by user/product, then by product.

select product,
    sum(cnt = 1) bought_once,
    sum(cnt = 2) bought_twice
from (
    select product, count(*) cnt
    from mydata 
    group by product, user_id
) d
group by product