137 lines
4.3 KiB
Plaintext
137 lines
4.3 KiB
Plaintext
|
/* Question 1 */
|
||
|
|
WITH (
|
||
|
|
A1 := CountryCovid WHERE year = 2021,
|
||
|
|
A2 := CountryCovid { country, month, total_cases } RENAME { month AS month2, total_cases AS total_cases2 },
|
||
|
|
A3 := A1 join A2 WHERE month < month2,
|
||
|
|
A4 := A3 { country, year, month, total_cases },
|
||
|
|
A5 := A1 NOT MATCHING A4,
|
||
|
|
A6 := A5 { country, total_cases },
|
||
|
|
|
||
|
|
B1 := A6 JOIN Encompasses,
|
||
|
|
B2 := SUMMARIZE B1 BY { continent }: { total_cases := SUM(total_cases) },
|
||
|
|
|
||
|
|
C1 := Continent { name } RENAME { name AS continent },
|
||
|
|
C2 := C1 NOT MATCHING B2,
|
||
|
|
C3 := EXTEND C2: { total_cases := 0 },
|
||
|
|
|
||
|
|
D1 := (B2 UNION C3) RENAME { total_cases AS covid_cases },
|
||
|
|
|
||
|
|
E1 := Independence { country },
|
||
|
|
E2 := E1 JOIN Encompasses,
|
||
|
|
E3 := SUMMARIZE E2 BY { continent }: { countries := COUNT() },
|
||
|
|
|
||
|
|
F1 := C1 NOT MATCHING E3,
|
||
|
|
F2 := EXTEND F1: { countries := 0 },
|
||
|
|
|
||
|
|
G1 := E3 UNION F2,
|
||
|
|
|
||
|
|
H1 := D1 JOIN G1
|
||
|
|
|
||
|
|
): H1
|
||
|
|
{ continent, countries, covid_cases }
|
||
|
|
|
||
|
|
/* Question 2 */
|
||
|
|
WITH (
|
||
|
|
A1 := GeoMountain { mountain, country },
|
||
|
|
A2 := SUMMARIZE A1 BY { mountain }: { cnt := COUNT() },
|
||
|
|
A3 := (A2 WHERE cnt > 1) { mountain },
|
||
|
|
|
||
|
|
B1 := A3 JOIN (A1 RENAME { country AS c1 }),
|
||
|
|
B2 := B1 JOIN (A1 RENAME { country AS c2 }),
|
||
|
|
|
||
|
|
C1 := (B2 RENAME { c1 AS code } ) JOIN Country,
|
||
|
|
C2 := C1 { mountain, c2, name } RENAME { name AS country1 },
|
||
|
|
C3 := (C2 RENAME { c2 AS code }) JOIN Country,
|
||
|
|
C4 := C3 { mountain, country1, name } RENAME { name AS country2 },
|
||
|
|
|
||
|
|
D1 := C4 WHERE country1 < country2
|
||
|
|
): D1
|
||
|
|
|
||
|
|
/* Question 3 */
|
||
|
|
WITH (
|
||
|
|
A1 := Country WHERE population > 200000000,
|
||
|
|
A2 := A1 { code, name } RENAME { code AS country },
|
||
|
|
A3 := A2 JOIN Encompasses WHERE continent = "Asia",
|
||
|
|
A4 := A3 { country, name },
|
||
|
|
|
||
|
|
B1 := CountryCovid WHERE month = 12,
|
||
|
|
B2 := A4 JOIN B1,
|
||
|
|
B3 := B2 { country, name, year, total_cases },
|
||
|
|
B3_2020 := B3 WHERE year = 2020,
|
||
|
|
B3_2021 := B3 WHERE year = 2021,
|
||
|
|
B3_2022 := B3 WHERE year = 2022,
|
||
|
|
|
||
|
|
C1 := EXTEND A4: { cases_2020 := MAX((B3_2020 RENAME { country AS code }) WHERE code = country, total_cases) },
|
||
|
|
C2 := EXTEND C1: { cases_2021 := MAX((B3_2021 RENAME { country AS code }) WHERE code = country, total_cases) },
|
||
|
|
C3 := EXTEND C2: { cases_2022 := MAX((B3_2022 RENAME { country AS code }) WHERE code = country, total_cases) },
|
||
|
|
|
||
|
|
D1 := EXTEND C3: { inc_2021 := cases_2021 - cases_2020 },
|
||
|
|
D2 := EXTEND D1: { inc_2022 := cases_2022 - cases_2021 },
|
||
|
|
|
||
|
|
E1 := D2 WHERE inc_2021 > inc_2022,
|
||
|
|
E2 := E1 { name } RENAME { name AS country }
|
||
|
|
): E2
|
||
|
|
|
||
|
|
/* Question 4 */
|
||
|
|
WITH (
|
||
|
|
A1 := Province WHERE area < 5000.0,
|
||
|
|
A2 := A1 { country } RENAME { country AS country1 },
|
||
|
|
|
||
|
|
B1 := A2 JOIN Borders,
|
||
|
|
B2 := B1 { country2 } RENAME { country2 AS country },
|
||
|
|
|
||
|
|
C1 := B2 JOIN Language,
|
||
|
|
C2 := C1 { country, name } RENAME { name AS language }
|
||
|
|
): C2
|
||
|
|
|
||
|
|
/* Question 5 */
|
||
|
|
WITH (
|
||
|
|
A1 := SUMMARIZE Borders BY { country1 }: { cnt := COUNT() },
|
||
|
|
A2 := A1 WHERE cnt = 3,
|
||
|
|
A3 := A2 { country1 } JOIN Borders,
|
||
|
|
A4 := A3 { country1, country2 } RENAME { country1 AS code },
|
||
|
|
|
||
|
|
B1 := EXTEND (A2 { country1 }): { n1 := MIN(A4 WHERE country1 = code, country2)},
|
||
|
|
B2 := EXTEND B1: { n3 := MAX(A4 WHERE country1 = code, country2)},
|
||
|
|
|
||
|
|
C1 := A3 NOT MATCHING (B2 RENAME { n1 AS country2 }),
|
||
|
|
C2 := C1 NOT MATCHING (B2 RENAME { n3 AS country2 }),
|
||
|
|
|
||
|
|
D1 := B2 JOIN C2,
|
||
|
|
D2 := D1 { country1, n1, country2, n3 } RENAME { country1 AS country, country2 AS n2 },
|
||
|
|
|
||
|
|
E1 := D2 RENAME { n1 AS country1, n2 AS country2 },
|
||
|
|
E2 := E1 JOIN Borders { country1, country2 },
|
||
|
|
E3 := E2 RENAME { country1 AS n1, country2 AS n2 },
|
||
|
|
|
||
|
|
F1 := E3 RENAME { n1 AS country1, n3 AS country2 },
|
||
|
|
F2 := F1 JOIN Borders { country1, country2 },
|
||
|
|
F3 := F2 RENAME { country1 AS n1, country2 AS n3 },
|
||
|
|
|
||
|
|
G1 := F3 RENAME { n2 AS country1, n3 AS country2 },
|
||
|
|
G2 := G1 JOIN Borders { country1, country2 },
|
||
|
|
G3 := G2 RENAME { country1 AS n2, country2 AS n3 },
|
||
|
|
|
||
|
|
H1 := G3 { country }
|
||
|
|
): H1
|
||
|
|
|
||
|
|
/* Question 6 */
|
||
|
|
WITH (
|
||
|
|
A1 := CountryCovid WHERE year = 2022 AND month = 12,
|
||
|
|
A2 := Country { code, population } RENAME { code AS country },
|
||
|
|
A3 := A1 JOIN A2,
|
||
|
|
|
||
|
|
B1 := EXTEND A3: { rate := CAST_AS_RATIONAL(total_cases) / (CAST_AS_RATIONAL(population) / 100000.0) },
|
||
|
|
B2 := B1 { country, rate } RENAME { country AS country1 },
|
||
|
|
B3 := B2 RENAME { country1 AS country2, rate AS n_rate },
|
||
|
|
|
||
|
|
C1 := B2 JOIN Borders { country1, country2 },
|
||
|
|
C2 := C1 JOIN B3,
|
||
|
|
|
||
|
|
D1 := EXTEND C2: { higher := IF rate > n_rate THEN 0 ELSE 1 END },
|
||
|
|
D2 := SUMMARIZE D1 BY { country1 }: { cnt := SUM(higher) },
|
||
|
|
D3 := D2 WHERE cnt = 0,
|
||
|
|
D4 := D3 { country1 } RENAME { country1 AS country }
|
||
|
|
): D4
|
||
|
|
|