WITH yearly_counts AS (
  SELECT 
    EXTRACT(YEAR FROM date_joined) AS "year",
    country,
    COUNT(*) AS "count"
  FROM 
    @kaggle.rajkumarpandey02_complete_list_of_unicorn_companies.unicorn
  GROUP BY 
    EXTRACT(YEAR FROM date_joined), country
)
SELECT 
  "year",
  SUM("count") AS total_per_year,
  SUM(CASE WHEN country = 'China' THEN "count" ELSE 0 END) AS china_count,
  SUM(CASE WHEN country = 'United States' THEN "count" ELSE 0 END) AS us_count,
  SUM(CASE WHEN country IN (
    'Germany', 'France', 'United Kingdom', 'Italy', 'Spain', 'Netherlands', 
    'Switzerland', 'Sweden', 'Belgium', 'Ireland', 'Finland', 'Norway', 
    'Denmark', 'Poland', 'Austria', 'Portugal', 'Greece', 'Czech Republic',
    'Hungary', 'Romania', 'Luxembourg', 'Ukraine', 'Estonia', 'Latvia', 
    'Lithuania', 'Croatia', 'Slovakia', 'Slovenia', 'Bulgaria', 'Iceland',
    'Malta', 'Cyprus', 'Liechtenstein', 'Monaco') THEN "count" ELSE 0 END) AS europe_count
FROM 
  yearly_counts
GROUP BY 
  "year"
ORDER BY 
  "year"