2
0
AccuWeather’s DB on Snowflake marketplace features a full 5 day forecast of 10 randomly generated Zip Codes across the United States. In addition, AccuWeather’s weather data and forecasts focus on the impact to people and businesses, so they can make the best weather-impacted decisions. Moreover, Help organizations ,Prepare in advance of severe weather and optimize outdoor project schedules. Minimize risks, avoid costly shutdowns and detect disruptions before they occur.
As part of this project we will be analyzing the Weather Data and derive the business insights. Following four Use case we have worked to help identify and predict temperatures and rain forecast for each station/country.
Case1 :Minimum and Maximum Temperature Forecast:
Background: Above all, Business must think about their day schedule, and they want to know how cold it will be or hot on day. Moreover,Surface weather parameters like maximum and minimum temperatures play an important role in Business. Hence, prediction is required for the team to plan his day-to-day operation and can minimize any adverse impact due to variation in temperature.
Query 1: Firstly, To find out the Minimum Temperature and Maximum temperature for each station along with the Forecasting Date and Day.
Firstly Query Solution:
with
min_temp as (select english_name, min(temperature_minimum) min_temp, max(temperature_maximum) max_temp from accuweather_daily_forecast_demo
group by english_name),
min_temp_dt as (select a.english_name, a.n_days as MIN_N_DAY,a.forecast_date as MIN_TEMP_FORECAST_DATE,m.min_temp from accuweather_daily_forecast_demo a,
min_temp m where a.english_name = m.english_name and a.temperature_minimum = m.min_temp ),
max_temp_dt as (select a.english_name, a.n_days as MAX_N_DAY, a.forecast_date as MAX_TEMP_FORECAST_DATE,m.max_temp from accuweather_daily_forecast_demo a,
min_temp m where a.english_name = m.english_name and a.temperature_maximum = m.max_temp )
select min_temp_dt.english_name,min_temp_dt.MIN_N_DAY,min_temp_dt.MIN_TEMP_FORECAST_DATE,min_temp_dt.min_temp,
max_temp_dt.MAX_N_DAY,max_temp_dt.MAX_TEMP_FORECAST_DATE,max_temp_dt.max_temp
from min_temp_dt, max_temp_dt where min_temp_dt.english_name = max_temp_dt.english_name order by min_temp_dt.english_name;
Output:
Case 2: Minimum and Maximum Temperature Forecast for Specific Station
Background: We know variations in daily temperature is directly proportional or much effected by the local geography of station. Therefore, Based on the local topography of station we want to track Minimum and Maximum temperature for each station.
Query 2: Secondly, To find out the Minimum Temperature and Maximum temperature for specific station along with the Forecasting Date and Day.
Secondly Query Solution:
select a.english_name, to_date(a.forecast_date) as MIN_TEMP_FORECAST_DATE,b.min_temp,to_date(d.forecast_date) as MAX_TEMP_FORECAST_DATE, max_temp
from accuweather_daily_forecast_demo a,
(select english_name, min(temperature_minimum) min_temp from accuweather_daily_forecast_demo where english_name = 'Dallas' group by english_name) b,
accuweather_daily_forecast_demo d,
(select english_name, max(temperature_maximum) max_temp from accuweather_daily_forecast_demo where english_name = 'Dallas' group by english_name) c
where
a.english_name = b.english_name and
a.temperature_minimum = b.min_temp and
a.english_name = d.english_name and
d.temperature_maximum = c.max_temp
order by english_name;
Output:
Cases
Case 3: Rain Prediction with Intensity:
Background: Weather forecast is utmost importance for agricultural activities. Irrigation departments are concerned on rains, and these are unpredictable both in time and space. Therefore, We will find out the Rain probability (Day/Night/Both) for all stations. Also, will evaluate the Rain intensity on Rain probability days for planning weather-based agricultural practices.
Query 3: Thirdly, To find out the probability of Raining on each day/night or both time for Every station. However, Below Query will produce the output if there are any Rain expectations on every day and the intensity of Rain (Heavy, Light) in Day or Nighttime.
Thirdly Query Solution:
select english_name,forecast_Date, n_days,temperature_minimum,temperature_maximum,
case when day_precipitation_type = 'Rain' and night_precipitation_type = 'Rain' then 'Rain Expected on Day and Night'
when day_precipitation_type = 'Rain' and night_precipitation_type is null then 'Rain Expected in Day only'
when night_precipitation_type = 'Rain' and day_precipitation_type is null then 'Rain Expected in Night only'
Else 'No Rain'
end as "Rain Expectations",
case when day_precipitation_Intensity = 'Heavy' then 'Heavy rain is predicted in Day'
when day_precipitation_Intensity = 'Light' then 'Expected Light Rains in a day'
Else
'No Rain'
end as Rain_Intensity_in_Day,
case when night_precipitation_Intensity = 'Heavy' then 'Heavy rain is predicted in Night'
when night_precipitation_Intensity = 'Light' then 'Expected Light Rains in a day'
when night_precipitation_Intensity = 'Moderate' then 'Expected Showers in a day'
Else 'No Rain'
end as Rain_Intensity_in_night
from accuweather_daily_forecast_demo;
Output:
Case 4: Number of Rainy Days for Specific Station:
Background: At higher level, Indian farmers are very much interested in knowing total number of days when there are chances of Rains. However, They are more concern in individual data like Rain chances on day, chances on night or on both days. Also want this data to be segregated based on the local topography of station.
Query 4: To find out the total number of days when Rain is predicted for every station.
Finally Query Solution:
select english_name,
sum(case when (day_precipitation_type = 'Rain' and night_precipitation_type is null) then 1 else 0 end) as "Number of Days when Rain will be in Day",
sum(case when (night_precipitation_type = 'Rain' and day_precipitation_type = 'Rain') then 1 else 0 end) as "Number of Days when Rain will be in Night",
"Number of Days when Rain will be in Night" + "Number of Days when Rain will be in Day" as "Total Number of Days Rain Expected"
from accuweather_daily_forecast_demo
group by english_name;
In conclusion, above are the use cases we developed based on AccuWeather’s DB.
