1
2WITH yearly_counts AS (
3  SELECT 
4    EXTRACT(YEAR FROM date_joined) AS "year",
5    country,
6    COUNT(*) AS "count"
7  FROM 
8    @kaggle.rajkumarpandey02_complete_list_of_unicorn_companies.unicorn
9  GROUP BY 
10    EXTRACT(YEAR FROM date_joined), country
11)
12SELECT 
13  "year",
14  SUM("count") AS total_per_year,
15  SUM(CASE WHEN country = 'China' THEN "count" ELSE 0 END) AS china_count,
16  SUM(CASE WHEN country = 'United States' THEN "count" ELSE 0 END) AS us_count,
17  SUM(CASE WHEN country IN (
18    'Germany', 'France', 'United Kingdom', 'Italy', 'Spain', 'Netherlands', 
19    'Switzerland', 'Sweden', 'Belgium', 'Ireland', 'Finland', 'Norway', 
20    'Denmark', 'Poland', 'Austria', 'Portugal', 'Greece', 'Czech Republic',
21    'Hungary', 'Romania', 'Luxembourg', 'Ukraine', 'Estonia', 'Latvia', 
22    'Lithuania', 'Croatia', 'Slovakia', 'Slovenia', 'Bulgaria', 'Iceland',
23    'Malta', 'Cyprus', 'Liechtenstein', 'Monaco') THEN "count" ELSE 0 END) AS europe_count
24FROM 
25  yearly_counts
26GROUP BY 
27  "year"
28ORDER BY 
29  "year"