-- CHAINED QUERY METHOD 1
WITH grouped_table AS 
(
    SELECT 
        sector,
        CASE WHEn Sector LIKE 'Consumer%' THEN 'Consumer'ELSE 'other' END AS NewSector,
        COUNT(DISTINCT symbol) AS NrOfCompanies
    FROM SP500 
    GROUP BY sector )
SELECT *
FROM grouped_table
WHERE NrOfCompanies > 50;

-- CHAINED QUERY METHOD 2
SELECT 
	Sector, 
    NewSector,
    NrOfCompanies
FROM 
    (
    SELECT 
        sector,
        CASE WHEn Sector LIKE 'Consumer%' THEN 'Consumer' ELSE 'other'END AS NewSector,
        COUNT(DISTINCT symbol) AS NrOfCompanies
    FROM SP500 
    GROUP BY sector
      )
WHERE NrOfCompanies > 50
  ;