Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
908 B
6
Indexable
Never
#1
SELECT t.year AS year, AVG(o.value) AS avg_prcp
FROM factobservation AS o
JOIN dimtime AS t ON o.time_id = t.date_id
JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'PRCP'
JOIN dimstation AS s ON o.station_id = s.id AND s.country_name = 'Portugal'
GROUP BY year
ORDER BY year ASC;

#2 
SELECT s.country_name AS country, t.year AS year, AVG(o.value) AS avg_max_temp
FROM factobservation AS o
JOIN dimstation AS s ON o.station_id = s.id
JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'TMAX'
JOIN dimtime AS t ON o.time_id = t.date_id
GROUP BY country, t.year
ORDER BY avg_max_temp DESC LIMIT 1;

#3
SELECT t.day_week AS weekday, t.year AS year, AVG(o.value) AS avg_prcp
FROM factobservation AS o
JOIN dimtime AS t ON o.time_id = t.date_id
JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'PRCP'
GROUP BY t.day_week, t.year
ORDER BY avg_prcp DESC LIMIT 1;