the above image shows a table with daily weather data over the last 6 months of 2020, including the maximum, minimum, and average temperatures.
Write a query that gives month, monthly maximum, monthly minimum, and monthly average temperatures for the six months.
Note: Round the average to the nearest integer.
Solution:
SELECT
SUBSTRING(record_date, 1, 7) AS month,
MAX(CASE WHEN data_type = 'max' THEN data_value END) AS monthly_max,
MIN(CASE WHEN data_type = 'min' THEN data_value END) AS monthly_min,
ROUND(AVG(CASE WHEN data_type = 'avg' THEN data_value END)) AS monthly_avg
FROM
temperature_records
GROUP BY
SUBSTRING(record_date, 1, 7)
ORDER BY
month;
Explanation:
1. SUBSTRING(record_date, 1, 7) extracts the year and month part of the record_date.
2. MAX(CASE WHEN data_type = 'max' THEN data_value END) calculates the maximum temperature for each month.
3. MIN(CASE WHEN data_type = 'min' THEN data_value END) calculates the minimum temperature for each month.
4. ROUND(AVG(CASE WHEN data_type = 'avg' THEN data_value END)) calculates the average temperature for each month and rounds it to the nearest integer.
5. GROUP BY SUBSTRING(record_date, 1, 7) groups the records by year and month.
6. ORDER BY month orders the result by month.
Hacker Rank Weather Analysis Problem Solution
