Simple Data Analysis – SQL

Listen to this article:

Weather trends using moving average and SQL queries

SQL Query: 

Select * from global_data

Select * from city_data

The tool used to create line chart and calculations: Google Sheet

For city data, delete city and country columns as they were repeated and there was only one city and country before calculating the moving average. 

Moving Average (MA) was calculated based on a 7 years average. 

Formula used:

For global data =average (B9:B15) and that same formula was extended to next cell like B10:B16…B268:B274

For City data = B2:B8….B266:B272

Key Considerations: 

Year should go on the X-axis and the value of average temperature should be displayed on the Y-axis.

Select Min and Max values based on the global and city data average of MA to display it nicely on the line chart to lessen the flat curve and show more variations for the comparison and trend study purpose to compare global and city-data.

Add a trendline to understand the increase in temperature and if that in parallel for both global and city curves.

For user experience, assigning meaningful titles, labels, colors, and legend locations. 

Observations:

The overall average for global data and Washington city temperatures are showing similar trends in the rise of average temperature. 

The rise of temperature is constant when you study the trendline for global and Washington City temperatures. The world is getting hotter.

Global temperature is overall cooler than Washington’s temperature.

Global Temperature 

Min = 3.15 (Year – 1779)

Max = 14.19 (Year – 2015)

Washington City Temperature

Min = 5.45 (Year – 1752)

Max = 14.19 (Year – 2013)

1950 to 2000, global temperature had a consistent drop which started rising again. 

Minimum of global and city temperatures were recorded in mid to late 1700 and maximum recorded in recent years.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner
Bitnami