Mysql group based on other column
Use COUNT and DISTINCT


CREATE TABLE table1 (
  `user_id` INTEGER,
  `product` VARCHAR(1)
);

INSERT INTO table1
  (`user_id`, `product`)
VALUES
  ('1', 'A'),
  ('1', 'A'),
  ('1', 'B'),
  ('2', 'A'),
  ('2', 'B'),
  ('3', 'A'),
  ('3', 'B'),
  ('3', 'B'),
  ('3', 'C'),
  ('3', 'D');




SELECT `user_id`, COUNT(DISTINCT `product`) FROM table1 GROUP BY `user_id`


user_id | COUNT(DISTINCT `product`)
------: | ------------------------:
      1 |                         2
      2 |                         2
      3 |                         4


db&lt;&gt;fiddle here