Untitled

-- exercicio 4 
-- o enUnciado é meio confuso, é a avg das observaçoes dos continentes ou dos elementos?
-- não está acabado

SELECT s.continent_name AS continent, e.name AS element, COUNT(o.VALUE) AS contagem
FROM factobservation AS o
JOIN dimelement AS e ON o.element_id = e.id
JOIN dimstation AS s ON o.station_id = s.id
JOIN dimtime AS t ON o.time_id = t.date_id
GROUP BY continent, element
ORDER BY continent;


-- exercicio 5
-- é preciso calcular o mes com valor máximo para cada pais 
-- normalmente seria MAX(dif1) ... GROUP BY MONTH
-- mas não consigo por isso a funcionar

SELECT s.country_name AS country, t.month AS month,
  avg(CASE WHEN e.name = "TMAX" THEN o.value END) - avg(CASE WHEN e.name = "TMIN" THEN o.value END) AS dif1
FROM factobservation AS o
JOIN dimelement AS e ON o.element_id = e.id
JOIN dimstation AS s ON o.station_id = s.id
JOIN dimtime AS t ON o.time_id = t.date_id
GROUP BY country, month
ORDER BY dif1 DESC