1SELECT 
2  YEAR(ratings_t."date") AS "year", 
3  "household_rating" AS "rating", 
4  "cost_of_30_second_ad_usd" AS "cost per 30s (nominal)", 
5  COUNT(*) FILTER (WHERE "use_sex") AS "sexy", 
6  COUNT(*) FILTER (WHERE "patriotic") AS "patriotic", 
7  COUNT(*) FILTER (WHERE "funny") AS "funny",
8  COUNT(*) FILTER (WHERE "celebrity") AS "celebrity",
9  COUNT(*) FILTER (WHERE "animals") AS "animals"
10FROM @kaggle.mattop_super_bowl_television_ratings.super_bowl_ratings ratings_t
11LEFT JOIN @kaggle.thedevastator_uncover_america_s_secrets_through_super_bowl_ads.superbowl_ads ads_t
12  ON YEAR(ratings_t."date") = ads_t."year"
13WHERE "year" BETWEEN 2000 AND 2020
14GROUP BY 
15  YEAR(ratings_t."date"), 
16  "household_rating", 
17  "cost_of_30_second_ad_usd"
18ORDER BY "year"
19