text: SELECT CASE WHEN zipcount < 100 THEN 'other'
         ELSE zip
         END AS zip_recoded,
         sum(zipcount) AS zipsum
    FROM (SELECT zip, count(*) AS zipcount
            FROM evanston311
           GROUP BY zip) AS fullcounts
   GROUP BY zip_recoded
   ORDER BY zipsum DESC;
  
  
  -- Fill in the command below with the name of the temp table
  DROP TABLE IF EXISTS recode;
  
  -- Create and name the temporary table
  CREATE TEMP TABLE recode AS
  -- Write the select query to generate the table 
  -- with distinct values of category and standardized values
    SELECT DISTINCT category, 
           RTRIM(SPLIT_PART(category, '-', 1)) AS standardized
      -- What table are you selecting the above values from?
      FROM evanston311;
      
  -- Look at a few values before the next step
  SELECT DISTINCT standardized 
    FROM recode
   WHERE standardized LIKE 'Trash%Cart'
      OR standardized LIKE 'Snow%Removal%';
  
  
  -- Code from previous step
  DROP TABLE IF EXISTS recode;
  
  CREATE TEMP TABLE recode AS
    SELECT DISTINCT category, 
           RTRIM(SPLIT_PART(category, '-', 1)) AS standardized
      FROM evanston311;
  
  -- Update to group trash cart values
  UPDATE recode 
     SET standardized='Trash Cart' 
   WHERE standardized LIKE 'Trash%Cart';
  
  -- Update to group snow removal values
  UPDATE recode 
     SET standardized='Snow Removal' 
   WHERE standardized LIKE 'Snow%';
      
  -- Examine effect of updates
  SELECT DISTINCT standardized 
    FROM recode
   WHERE standardized LIKE 'Trash%Cart'
      OR standardized LIKE 'Snow%Removal%';
  
  
  -- Code from previous step
  DROP TABLE IF EXISTS recode;
  
  CREATE TEMP TABLE recode AS
    SELECT DISTINCT category, 
           rtrim(split_part(category, '-', 1)) AS standardized
      FROM evanston311;
    
  UPDATE recode SET standardized='Trash Cart' 
   WHERE standardized LIKE 'Trash%Cart';
  
  UPDATE recode SET standardized='Snow Removal' 
   WHERE standardized LIKE 'Snow%Removal%';
  
  -- Update to group unused/inactive values
  UPDATE recode 
     SET standardized='UNUSED' 
   WHERE standardized IN ('THIS REQUEST IS INACTIVE...Trash Cart', 
                 '(DO NOT USE) Water Bill',
                 'DO NOT USE Trash', 
                'NO LONGER IN USE');
  
  -- Examine effect of updates
  SELECT DISTINCT standardized 
    FROM recode
   ORDER BY standardized;
  
  
  
  -- Code from previous step
  DROP TABLE IF EXISTS recode;
  CREATE TEMP TABLE recode AS
    SELECT DISTINCT category, 
           rtrim(split_part(category, '-', 1)) AS standardized
    FROM evanston311;
  UPDATE recode SET standardized='Trash Cart' 
   WHERE standardized LIKE 'Trash%Cart';
  UPDATE recode SET standardized='Snow Removal' 
   WHERE standardized LIKE 'Snow%Removal%';
  UPDATE recode SET standardized='UNUSED' 
   WHERE standardized IN ('THIS REQUEST IS INACTIVE...Trash Cart', 
                 '(DO NOT USE) Water Bill',
                 'DO NOT USE Trash', 'NO LONGER IN USE');
  
  -- Select the recoded categories and the count of each
  SELECT standardized, COUNT(*)
  -- From the original table and table with recoded values
    FROM evanston311 
         LEFT JOIN recode 
         -- What column do they have in common?
         ON evanston311.category=recode.category
   -- What do you need to group by to count?
   GROUP BY standardized
   -- Display the most common val values first
   ORDER BY COUNT DESC;
  
  
  -- To clear table if it already exists
  DROP TABLE IF EXISTS indicators;
  
  -- Create the indicators temp table
  CREATE TEMP TABLE indicators AS
    -- Select id
    SELECT id, 
           -- Create the email indicator (find @)
           CAST (description LIKE '%@%' AS integer) AS email,
           -- Create the phone indicator
           CAST (description LIKE '%__-___-___%' AS integer) AS phone 
      -- What table contains the data? 
      FROM evanston311;
  
  -- Inspect the contents of the new temp table
  SELECT *
    FROM indicators;
  
  
  
  -- To clear table if it already exists
  DROP TABLE IF EXISTS indicators;
  
  -- Create the temp table
  CREATE TEMP TABLE indicators AS
    SELECT id, 
           CAST (description LIKE '%@%' AS integer) AS email,
           CAST (description LIKE '%___-___-____%' AS integer) AS phone 
      FROM evanston311;
    
  -- Select the column you'll group by
  SELECT priority,
         -- Compute the proportion of rows with each indicator
         SUM(email)/count(*)::numeric AS email_prop, 
         SUM(phone)/count(*)::numeric AS phone_prop
    -- Tables to select from
    FROM evanston311
         LEFT JOIN indicators
         -- Joining condition
         ON evanston311.id=indicators.id
   -- What are you grouping by?
   GROUP BY priority;
   
  