Untitled

-- exercicio 5

WITH TMINSelect as (
    SELECT s.country_name AS country,t.month as month, o.time_id as time, o.value as value
    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
    WHERE e.name = 'TMIN'
), TMAXSelect as (
    SELECT s.country_name AS country,t.month as month, o.time_id as time, o.value as value
    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
    WHERE e.name = 'TMAX'
), AveragesPerMonth AS (
    SELECT AVG(TMAXSelect.VALUE - TMINSelect.Value) AS dif, TMAXSelect.country AS fCountry, TMAXSelect.month as fMonth
FROM TMAXSelect,TMINSelect
WHERE TMAXSelect.country = TMINSelect.country AND TMAXSelect.time = TMINSelect.time
GROUP BY fCountry, fMonth
)
SELECT fCountry AS Country, fMonth AS Month, dif AS TemperatureRange FROM AveragesPerMonth AS AVG1
WHERE dif = (SELECT MAX(dif) FROM AveragesPerMonth AS AVG2 WHERE AVG2.fCountry = AVG1.fCountry)
ORDER BY Country;