SELECT DISTINCT (p.products_id),
			 p.new_arrivals,
			 p.sku,p.vendor_sku,p.is_360_video,p.video_360_cnt,p.product_type,p.product_name,p.center_stone_name,p.center_stone_color,p.diamond_carat_weight,p.clearance,p.style,p.product_gemstone,p.product_url,p.product_center_stone_shape,p.diamond_carat_weight,p.diamond_quality,p.metal_type,
			 prod_group_var as group_var,
			 p.metal_group,
			 case when p.metal_group != '' then CONCAT(p.metal_group,'-',p.diamond_carat_weight,'-',p.diamond_quality,'-',p.product_gemstone,'-',p.product_type,'-',p.product_center_stone_shape,'-',p.width_mm,'-',p.product_center_stone_name,'-',p.zodiac_sign) else p.products_id end as group_var2,
			 p.sale_price,p.our_price,p.retail_price,p.is_build,p.current_stock,p.bc_category_id,pcr.category_id,p.dial_color,p.istryathome,p.tryathomeqty,p.matching_ring_sku,p.matching_band_sku,p.color,p.product_type_group,
			 (CASE p.metal_type  
												WHEN '14K White Gold' THEN 1
												WHEN '14K Yellow Gold' THEN 2
												WHEN '14K Rose Gold' THEN 3
												WHEN '18K White Gold' THEN 4
												WHEN '18K Yellow Gold' THEN 5
												WHEN '18K Rose Gold' THEN 6
												WHEN 'Palladium' THEN 7
												WHEN 'Platinum' THEN 8
												ELSE 9 END) AS metal_rank, p.group_metals, p.group_gemstones, p.created_metal_gemstone_group, (CASE p.clearance  WHEN 'No' THEN p.display_position ELSE 999999 END) AS ship_in_two_days_rank , SUM(p.prod_sold_quantity) as total_sold_quantity , case when (p.sale_price > 2000 or (p.sale_price <= 0 and p.our_price > 2000)) 
						then 
							case when (p.prod_sold_quantity >= 3 and DATE_FORMAT(p.prod_last_order_date,'%Y-%m-%d') >= '2022-04-30') then 1 else 0 end  
						else
							case when (p.prod_sold_quantity >= 5 and DATE_FORMAT(p.prod_last_order_date,'%Y-%m-%d') >= '2022-04-30') then 1 else 0 end
						end as best_seller_flag  FROM  ( SELECT DISTINCT psub.products_id,
											 (CASE psub.metal_type  
												WHEN '14K White Gold' THEN 1
												WHEN '14K Yellow Gold' THEN 2
												WHEN '14K Rose Gold' THEN 3
												WHEN '18K White Gold' THEN 4
												WHEN '18K Yellow Gold' THEN 5
												WHEN '18K Rose Gold' THEN 6
												WHEN 'Palladium' THEN 7
												WHEN 'Platinum' THEN 8
												ELSE 9 END) AS metal_rank ,(CASE psub.clearance  WHEN 'No' THEN psub.display_position ELSE 999999 END) AS ship_in_two_days_rank, 
												IF(psub.current_stock <=0,999999,1) AS stock_rank , IF(psub.prod_sold_quantity > 0,psub.prod_sold_quantity,0) AS prod_sold_quantity  FROM `sky_products` AS psub  inner join `sky_products_category` AS spcr on (psub.products_id = spcr.products_id) inner join `sky_category` AS psubc on (spcr.category_id = psubc.category_id) WHERE psub.status='1' AND spcr.category_id IN('29')  AND psub.clearance_fixed_sku = '0' AND psub.clearance = 'No' AND ( psub.style REGEXP "^Nature|^Nature#|#Nature|![\ ]Nature#"  ) AND ( psub.material REGEXP "^Diamond|^Diamond#|#Diamond|![\ ]Diamond#"  ) ORDER BY (CASE WHEN psub.prod_sold_quantity >0 THEN psub.prod_sold_quantity WHEN psub.vendor_name = 'shy' THEN -1 ELSE 0 END) DESC, metal_rank, psub.display_position ) AS pn, `sky_products` AS p ,`sky_products_category` AS pcr  , `sky_category` AS c  WHERE p.products_id = pcr.products_id  AND p.status='1' AND p.vendor_sku NOT IN('SAMPLE-1','SAMPLE-2','SAMPLE-3','SAMPLE-4','SAMPLE-5','SAMPLE-6','SAMPLE-7','SAMPLE-8','SAMPLE-9','SAMPLE-10')  AND pcr.category_id = c.category_id AND c.status = '1' AND p.designer != 'invicta'  AND (CASE WHEN p.clearance ='Yes' THEN 1 ELSE p.current_stock>0 END)  AND p.products_id = pn.products_id AND pcr.category_id IN('29')  AND p.clearance_fixed_sku = '0' AND p.clearance = 'No' AND ( p.style REGEXP "^Nature|^Nature#|#Nature|![\ ]Nature#"  ) AND ( p.material REGEXP "^Diamond|^Diamond#|#Diamond|![\ ]Diamond#"  ) GROUP BY (CASE group_var WHEN '' THEN p.products_id ELSE group_var END)  ORDER BY (CASE WHEN SUM(pn.prod_sold_quantity) >0 THEN SUM(pn.prod_sold_quantity) WHEN p.vendor_name = 'shy' THEN -1 ELSE 0 END) DESC, metal_rank, p.display_position  LIMIT 0 , 24