Untitled

---3
--I
SELECT first_year
FROM inventory
WHERE element = 'PRCP'
ORDER BY first_year ASC LIMIT 1;

--II
SELECT s.id, s.name
FROM station AS s
JOIN inventory AS i ON s.id = i.station AND i.first_year = (
    SELECT MIN(first_year) 
    FROM inventory
    WHERE element = 'PRCP'
  );


--III
SELECT c.name AS continent, COUNT(*) AS count
FROM Station AS s, Country AS p, Continent AS c
WHERE s.country = p.fips AND p.continent = c.cc
GROUP BY c.name ORDER BY count DESC;

--IV
SELECT
   YEAR(date_id) AS year,
   AVG(TMAX) AS avg_max_temp
FROM Observation AS o, Station AS s
WHERE o.station = s.id AND s.country = 'PO'
GROUP BY YEAR(date_id)
ORDER BY YEAR(date_id) ASC;

--V
SELECT
   MONTH(date_id) AS month,
   AVG(PRCP) AS precipitation,
   MAX(TMAX) AS max_temp,
   MIN(TMIN) AS min_temp,
   MAX(TMAX) - MIN(TMIN) AS temp_amplitude
FROM Observation AS o, Station AS s
WHERE o.date_id >= 2013-01-01 AND o.station = s.id AND s.country = 'UK'
GROUP BY MONTH(date_id)
ORDER BY MONTH ASC;

--VI
SELECT
   AVG(CASE WHEN YEAR(date_id) = 2021 THEN PRCP END)
     - AVG(CASE WHEN YEAR(date_id) = 2006 THEN PRCP END) AS diff_avg_precip,
   AVG(CASE WHEN YEAR(date_id) = 2021 THEN TMAX END)
     - AVG(CASE WHEN YEAR(date_id) = 2006 THEN TMAX END) AS diff_max_temp,
   AVG(CASE WHEN YEAR(date_id) = 2021 THEN TMIN END)
     - AVG(CASE WHEN YEAR(date_id) = 2006 THEN TMIN END) AS diff_min_temp
FROM Observation AS o, Station AS s
WHERE YEAR(date_id) IN (2006,2021) AND o.station = s.id AND s.country = 'UK';

---4
--I dimtime
SELECT 
    date_id AS date,
    year(date_id) AS year,
    quarter(date_id) AS quarter,
    month(date_id) AS month,
    day(date_id) AS day,
    dayofweek(date_id) AS day_week,
    dayofyear(date_id) AS day_year,
    (CASE 
      WHEN (day(date_id)>=21 AND month(date_id)=12) OR (month(date_id)<3) OR (month(date_id)=3 AND day(date_id)<21) THEN "winter"
      WHEN (day(date_id)>=21 AND month(date_id)=3) OR (month(date_id)<6 AND month(date_id)>3) OR (month(date_id)=6 AND day(date_id)<21) THEN "spring"
      WHEN (day(date_id)>=21 AND month(date_id)=6) OR (month(date_id)>6 AND month(date_id)<9) OR (month(date_id)=9 AND day(date_id)<23) THEN "summer"
      ELSE "autumn"
    END) AS season
FROM observation
ORDER BY date_id ASC;

--II dimstation
SELECT 
    station.id AS id,
    station.name AS name,
    CASE WHEN country.fips = 'US' THEN state.alias ELSE 'XX' END AS state_alias,
    CASE WHEN country.fips = 'US' THEN state.name ELSE 'not applicable' END AS state_name,
    CASE WHEN country.fips = 'US' THEN state.population ELSE 0 END AS state_population,
    CASE WHEN country.fips = 'US' THEN state.total_area ELSE 0 END AS state_total_area,
    CASE WHEN country.fips = 'US' THEN state.land_area ELSE 0 END AS state_land_area,
    CASE WHEN country.fips = 'US' THEN state.water_area ELSE 0 END AS state_water_area,
    CASE WHEN country.fips = 'US' THEN state.nr_reps ELSE 0 END AS state_nr_reps,
    country.fips AS country_fips,
    country.name AS country_name,
    country.license_plate AS country_license_plate,
    country.domain AS country_domain,
    continent.cc AS continent_code,
    continent.name AS continent_name
FROM station
JOIN country ON station.country = country.fips
JOIN continent ON country.continent = continent.cc
JOIN state ON station.state = state.alias;

--III dimelement
SELECT 
  ROW_NUMBER() OVER (ORDER BY name) AS id,
  name,
  property,
  description,
  unit
FROM element;

--IV factobservation
SELECT * FROM (
  SELECT o.date_id, o.station AS station_id, e.id AS element_id, IFNULL(o.PRCP,0) AS value
  FROM (SELECT 
    ROW_NUMBER() OVER (ORDER BY name) AS id,
    name,
    property,
    description,
    unit
  FROM element) AS e
  JOIN observation AS o ON e.name = 'PRCP'
  UNION ALL
  SELECT o.date_id, o.station AS station_id, e.id AS element_id, IFNULL(o.TMAX,0) AS value
  FROM (SELECT 
    ROW_NUMBER() OVER (ORDER BY name) AS id,
    name,
    property,
    description,
    unit
  FROM element) AS e
  JOIN observation AS o ON e.name = 'TMAX'
  UNION ALL
  SELECT o.date_id, o.station AS station_id, e.id AS element_id, IFNULL(o.TMIN,0) AS value
  FROM (SELECT 
    ROW_NUMBER() OVER (ORDER BY name) AS id,
    name,
    property,
    description,
    unit
  FROM element) AS e
  JOIN observation AS o ON e.name = 'TMIN'
) AS fo
ORDER BY station_id, element_id, date_id;

---5
--I
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;

--II
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, year
ORDER BY avg_max_temp DESC LIMIT 1;

--III
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 weekday, year
ORDER BY avg_prcp DESC LIMIT 1;

--IV
SELECT element, AVG(num_obs) AS avg_number_obs
FROM (
    SELECT s.continent_name as continent, e.name as element, COUNT(*) AS num_obs
    FROM factobservation AS o
    JOIN dimelement AS e ON o.element_id = e.id
    JOIN dimstation AS s ON o.station_id = s.id 
    GROUP BY continent, element
) AS obs_counts
GROUP BY element;

--V
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;

--VI
SELECT 
	AVG(CASE WHEN t.year = 2006 AND e.name = "PRCP" THEN o.value END)
    - AVG(CASE WHEN t.year = 2021 AND e.name = "PRCP" THEN o.value END) AS diff_PCRP,
    AVG(CASE WHEN t.year = 2006 AND e.name = "TMAX" THEN o.value end) 
    -	AVG(CASE WHEN t.year = 2021 AND e.name = "TMAX" THEN o.value END) AS diff_TMAX,
	AVG(CASE WHEN t.year = 2006 AND e.name = "TMIN" THEN o.value END) 
    - AVG(CASE WHEN t.year = 2021 AND e.name = "TMIN" THEN o.value END) AS diff_TMIN
		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 s.country_name = "United Kingdom";