How to implement such a SQL query according to the description?

Question

SQL

This is not a school task or something. This is a real problem that I want to understand.
There is a table with weather parameters:

CREATE TABLE `weather` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `where_id` int(11) NOT NULL default '0',     # где снимались показания (идентификатор станции)
      `temperature` smallint(4) UNSIGNED NOT NULL default '0',  # температура воздуха в Кельвинах
      `precipitation` ENUM('none', 'rain', 'rain_snow', 'snowfall','hail') NOT NULL default 'none',   # осадки 
      `wind` smallint(4) UNSIGNED NOT NULL default '0',   # сила ветра в метрах в секунду
      `date` datetime NOT NULL default '0000-00-00 00:00:00', # точное время когда снималось показание
       PRIMARY KEY(`id`)
  ) ENGINE=MyISAM;

1) Get the day, week and month in which the temperature difference reaches its maximum value (for each station).
2) Get the day, week and month in which the temperature difference is greater than 10.
3) Calculate the average annual number of days with ice.
4) Optimize the table structure for the resulting queries.
How is all this resolved?

Answer 1

It looks like you have MySQL. If I had Postgres, I would use, for example, the capabilities of CTE . There is no such thing in Muskul, it seems that there are some kind of workrounds .
But it can be trite and primitive. For example, the answer to the first question:

SELECT * 
FROM (
  SELECT min(temperature) as min, max(temperature) as max, where_id, DATE_FORMAT(date,'%Y-%m-%d') as date from weather GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
) AS data
ORDER BY max - min DESC 
LIMIT 1;

As a result, we will see the date, place, and minimum and maximum temperatures at this point for a given day.
PS
It is possible in one request:

SELECT min(temperature) as minn, max(temperature) as maxx, where_id, DATE_FORMAT(date,'%Y-%m-%d') as date from weather GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
ORDER BY max(temperature) - min(temperature) DESC 
LIMIT 1;

PPS
Full answer to question 1:

SELECT 
  where_id, 
  (
    SELECT max(temperature) - min(temperature)
    FROM weather
    WHERE where_id = w1.where_id
    GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
    ORDER BY max(temperature) - min(temperature) DESC
    LIMIT 1
  ) as diff,
  DATE_FORMAT(date,'%Y-%m-%d') as date
FROM weather as w1
WHERE id = (
  SELECT MAX(id)
  FROM weather
  WHERE where_id = w1.where_id
  GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
  ORDER BY max(temperature) - min(temperature) DESC
  LIMIT 1
)

Answer 2

It is possible so still (the answer to 1 question). Just had to remove UNSIGNED