FSQ Culinary Category Aggregation for Optimized Hot Spot Analysis

Recategorization.sql

 -- Higher Level Aggregations
 
 , CASE
    WHEN ac1.level2_name IN ('American Restaurants','Burger Joints','Sandwich Places','BBQ Joints'
                            ,'Diners','Steakhouses','Seafood Restaurants','Hot Dog Joints','Cajun / Creole Restaurants','Southern / Soul Food Restaurants'
                            ,'Mac & Cheese Joints','Wings Joints','Salad Places','Fried Chicken Joints'
                            ) 
                            THEN 'American'

    WHEN ac1.level2_name IN ('Mexican Restaurants','Latin American Restaurants','Caribbean Restaurants') 
                            THEN 'Mexican_Latin_American_and_South_American'

    WHEN ac1.level2_name IN ('Afghan Restaurants','African Restaurants','Halal Restaurants','Jewish Restaurants','Falafel Restaurants','Kebab Restaurants','Middle Eastern Restaurants') 
                            THEN 'African_and_Middle_Eastern'

    WHEN ac1.level2_name IN ('Eastern European Restaurants','French Restaurants','German Restaurants','Austrian Restaurants','Italian Restaurants','Fish & Chips Shops','Creperies','Fondue Restaurants','Modern European Restaurants','Portuguese Restaurants','Scandinavian Restaurants','Polish Restaurants','Belgian Restaurants','Dutch Restaurants','Czech Restaurants','Swiss Restaurants','English Restaurants','Ukrainian Restaurants','Spanish Restaurants','Irish Pubs','Pizza Places','Russian Restaurants') 
                            THEN 'European'
                            
    WHEN ac1.level2_name IN ('Mediterranean Restaurants','Greek Restaurants','Turkish Restaurants') 
                            THEN 'Mediterranean'

    WHEN ac1.level2_name IN ('Indian Restaurants','Bangladeshi Restaurants','Pakistani Restaurants','Sri Lankan Restaurants') 
                            THEN 'Indian_Subcontinent'

   WHEN ac1.level2_name IN ('Asian Restaurants','Dumpling Restaurants','Hawaiian Restaurants') 
                            THEN 'Asian'
                            
   WHEN ac1.level2_name IN ('Fast Food Restaurants','Breakfast Spots','Bagel Shops','Soup Places','Gluten-free Restaurants','Molecular Gastronomy Restaurants','Cafeterias','Buffets','Comfort Food Restaurants','Theme Restaurants','Bistros','Vegetarian / Vegan Restaurants','Delis / Bodegas','Restaurants','Food Trucks','Food Courts','Gastropubs','Food Stands','Snack Places') 
                            THEN 'Non_Specific'

   WHEN ac1.level2_name IN ('Australian Restaurants','Poutine Places') 
                            THEN 'Australian_or_Canadian'
    
                            
    ELSE ac1.level2_name 
  END AS simple_cat
    
-- Asian Sub-Category Recategorizations

 , CASE
     WHEN cat_lvl_3 is null OR cat_lvl_3 IN ('Noodle Houses', 'Hotpot Restaurants','Satay Restaurants') THEN 'Unspecified_Asian_Restaurants'
     WHEN cat_lvl_3 IN ('Poke Places') THEN 'Hawaiian_or_Pacific_Islander_Restaurants'
     WHEN cat_lvl_3 IN ('Himalayan Restaurants','Tibetan Restaurants') THEN 'Tibetan_or_Himalayan_Restaurants'
     ELSE regexp_replace(cat_lvl_3,' ','_')
   END AS asian_category