PostegreSQL | An example of subquery with COUNT() and MAX()

postegre-sql-with-max-and-count.md

SELECT 
	lab1.product_name,
	COUNT("*") AS total_users
FROM
    (
	SELECT
		GROUP_CONCAT(lab2.plant_profile_id) AS teste,
		substring_index(group_concat(lab2.plant_profile_id SEPARATOR ','), ',', 1) as plant_id,
		substring_index(group_concat(lab2.product_name SEPARATOR ','), ',', 1) as product_name,
		lab2.user_id,
		MAX(lab2.percent) AS max_value
		FROM
		    (
		      SELECT
			  user_talent_plants.user_id AS user_id, 
			  SUM(talent_plants.product_value) AS percent, 
			  COUNT(*) AS total, talent_plants.plant_profile_id, 
			  talent_plants_profile.tplant_type AS product_name
		      FROM `user_talent_plants`
		      INNER JOIN `talent_plants` 
			  ON `user_talent_plants`.`talent_plant_id` = `talent_plants`.`id`
		      INNER JOIN `talent_plants_profile` 
			  ON `talent_plants`.`plant_profile_id` = `talent_plants_profile`.`id`
		       GROUP BY `talent_plants`.`plant_profile_id`, user_talent_plants.user_id
			  ORDER BY user_talent_plants.user_id ASC, `total` DESC, `percent` DESC
		     ) lab2
		GROUP BY lab2.user_id
	) lab1
GROUP BY lab1.product_name
ORDER BY total_users DESC