> For the complete documentation index, see [llms.txt](https://docs.predicthq.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.predicthq.com/integrations/third-party-integrations/snowflake/snowflake-data-science-guide/sql-method-guide.md).
# SQL method guide
Transforming Event Data into ML-Ready Features using SQL
## Using Snowflake SQL to recreate the Features API
This guide uses a publicly available PredictHQ event sample table called\
**PREDICTHQ\_EVENTS\_RETAIL\_LONDON**\
In all later instances, replace this table name with the name of the events data table that PredictHQ has provisioned as per the [Snowflake Secure Data Share](https://docs.predicthq.com/integrations/third-party-integrations/snowflake).
The rest of the guide also uses temporary tables but you can turn these tables into permanent tables as needed.
Once you have created **SAVED\_LOCATIONS** as described on the parent page of this guide, complete the following steps:
1. To use the input table with the code in this guide, modify its format
2. Generate daily aggregated statistics for each location by:
* attendance based features
* rank based features
* impact based features
* To match the desired granularity, update the date range in the following code examples:
* For training a machine learning model, update the dates to get historical data for the locations
* If running a model in production and forecasting future demand, update the dates for the visibility window of the forecast - e.g. the new week, month, or months
3. Join all features together in a single output table
## Step 1: Modify the input table format
Once the **SAVED\_LOCATIONS** input table is created, the following code shapes that table to be in a day by day format of the input called **SAVED\_LOCATIONS\_DAILY**:
{% code fullWidth="true" %}
```sql
----split the table out into having one day equals one row between the date range.
CREATE OR REPLACE TEMP TABLE saved_locations_daily AS
select
date(date_start) + value::int as date,
s.location,
s.lat,
s.lon,
s.radius,
lower(s.radius_unit) as radius_unit --forcing the lower in case of data entry mistakes
from saved_locations s,
table(flatten(array_generate_range(0, datediff('day', date_start, date_end) + 1))) t
;
```
{% endcode %}
## Step 2: Calculating daily aggregated ML features with SQL in Snowflake
The code calculates each Feature set in blocks. See the column headers in each code block for which Features are available to generate.
### Predicted Attendance features
The code calculates each value as the sum of Predicted Attendance for the day at a given location within the defined radius:
----Predicted Attendance features
CREATE OR REPLACE TEMP TABLE phq_attendance_features AS
WITH events_attended AS ( --Attendance Features for main 7 categories
SELECT
e.event_id,
s.date,
s.location,
e.category,
imp.value:value::int as phq_attendance
FROM predicthq.predicthq_events_retail_london e
RIGHT JOIN saved_locations_daily s
ON ST_DISTANCE(e.geo, ST_MAKEPOINT(s.lon, s.lat)) <=
CASE
WHEN s.radius_unit = 'mi' THEN s.radius * 1609.34
WHEN s.radius_unit = 'km' THEN s.radius * 1000
END
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_start)) <= s.date
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_end)) >= s.date,
latERAL FlatTEN(INPUT => e.IMPACT_PATTERNS) vert,
latERAL FlatTEN(INPUT => vert.value:impacts) imp
WHERE imp.value:date_local::DATE = s.date
AND vert.value:vertical::STRING = 'accommodation'
AND imp.value:position::STRING = 'event_day'
AND e.phq_attendance IS NOT NULL
AND e.category in ('community','concerts','conferences','expos','festivals','performing-arts','sports')
),
events_attended_other AS ( --Attendance Features for special categories
SELECT
e.event_id,
s.date,
s.location,
e.category,
e.phq_attendance,
CASE WHEN category = 'academic' and ARRAY_CONTAINS('social'::variant, e.labels) THEN 'social'
WHEN category = 'academic' and ARRAY_CONTAINS('social'::variant, e.labels) THEN 'graduation'
ELSE '' END as academic_split
FROM predicthq.predicthq_events_retail_london e
RIGHT JOIN saved_locations_daily s
ON ST_DISTANCE(e.geo, ST_MAKEPOINT(s.lon, s.lat)) <=
CASE
WHEN s.radius_unit = 'mi' THEN s.radius * 1609.34
WHEN s.radius_unit = 'km' THEN s.radius * 1000
END
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_start)) <= s.date
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_end)) >= s.date
WHERE e.phq_attendance IS NOT NULL
AND e.category in ('school-holidays','academic')
),
attendance_group AS ( --Group the 7 main categories daily and make sure a result is displayed each day even if it's just 0
SELECT
data_range.date,
data_range.location,
SUM(CASE WHEN a.category = 'community' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_community,
SUM(CASE WHEN a.category = 'concerts' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_concerts,
SUM(CASE WHEN a.category = 'conferences' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_conferences,
SUM(CASE WHEN a.category = 'expos' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_expos,
SUM(CASE WHEN a.category = 'festivals' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_festivals,
SUM(CASE WHEN a.category = 'performing-arts' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_performing_arts,
SUM(CASE WHEN a.category = 'sports' THEN a.phq_attendance ELSE 0 END) AS phq_attendance_sports
FROM (SELECT date, location FROM saved_locations_daily) data_range
LEFT JOIN events_attended a
ON data_range.date = a.date
AND data_range.location = a.location
GROUP BY data_range.date, data_range.location
ORDER BY data_range.date, data_range.location
),
attendance_group_other AS ( --Group the other categories daily and make sure a result is displayed each day even if it's just 0
SELECT
data_range.date,
data_range.location,
SUM(CASE WHEN ao.category = 'school-holidays' THEN ao.phq_attendance ELSE 0 END) AS phq_attendance_school_holidays,
SUM(CASE WHEN ao.academic_split = 'graduation' THEN ao.phq_attendance ELSE 0 END) AS phq_attendance_academic_graduation,
SUM(CASE WHEN ao.academic_split = 'social' THEN ao.phq_attendance ELSE 0 END) AS phq_attendance_academic_social
FROM (SELECT date, location FROM saved_locations_daily) data_range
LEFT JOIN events_attended_other ao
ON data_range.date = ao.date
AND data_range.location = ao.location
GROUP BY data_range.date, data_range.location
ORDER BY data_range.date, data_range.location
)
SELECT --final select for phq_attendance_features
ag.date,
ag.location,
ag.phq_attendance_community,
ag.phq_attendance_concerts,
ag.phq_attendance_conferences,
ag.phq_attendance_expos,
ag.phq_attendance_festivals,
ag.phq_attendance_performing_arts,
ag.phq_attendance_sports,
ago.phq_attendance_school_holidays,
ago.phq_attendance_academic_graduation,
ago.phq_attendance_academic_social
FROM attendance_group ag
LEFT JOIN attendance_group_other ago
ON ag.location = ago.location
AND ag.date = ago.date
;
SELECT * FROM phq_attendance_features order by location, date;
If you want metrics other than SUM, use the following code as a template for each column. The category name part of the code for each column (in these examples defaulted to ‘community’) changes depending on which Predicted Attendance feature you want to call. Refer to the column code in the earlier Predicted Attendance features code block for available Feature categories:
{% code title="Count" fullWidth="true" %}
```sql
COUNT(CASE WHEN a.category = 'community' THEN 1 ELSE NULL END)
```
{% endcode %}
{% code title="Average" fullWidth="true" %}
```sql
IFNULL(ROUND(AVG(CASE WHEN a.category = 'community' THEN a.phq_attendance ELSE NULL END),2),0)Some code
```
{% endcode %}
{% code title="Median" fullWidth="true" %}
```sql
IFNULL(ROUND(MEDIAN(CASE WHEN a.category = 'community' THEN a.phq_attendance ELSE NULL END),2),0)
```
{% endcode %}
{% code title="Standard Deviation" fullWidth="true" %}
```sql
IFNULL(ROUND(STDDEV(CASE WHEN a.category = 'community' THEN a.phq_attendance ELSE NULL END),2),0)
```
{% endcode %}
{% code title="Min and Max" fullWidth="true" %}
```sql
IFNULL(MIN(CASE WHEN a.category = 'community' THEN a.phq_attendance ELSE NULL END),0)
IFNULL(MAX(CASE WHEN a.category = 'community' THEN a.phq_attendance ELSE NULL END),0)
```
{% endcode %}
### PHQ Rank features
The code calculates values as a count of events occurring at each rank level, per day, per location. If an event occurs over multiple days, it has a result in each day until the event is over. The following code returns each rank level as its own column:
{% code title="PHQ Rank features" fullWidth="true" %}
```sql
----PHQ Rank features
CREATE OR REPLACE TEMP TABLE phq_rank_features as
WITH events_ranked AS ( --Pull ranked events within range
SELECT
e.event_id,
s.date,
s.location,
e.category,
e.phq_rank,
CASE WHEN category = 'academic' and ARRAY_CONTAINS('academic-session'::variant, e.labels) THEN 'session'
WHEN category = 'academic' and ARRAY_CONTAINS('exam'::variant, e.labels) THEN 'exam'
WHEN category = 'academic' and ARRAY_CONTAINS('holiday'::variant, e.labels) THEN 'holiday'
ELSE '' END AS academic_split,
CASE
WHEN e.phq_rank between 0 and 20 THEN 1
WHEN e.phq_rank between 21 and 40 THEN 2
WHEN e.phq_rank between 41 and 60 THEN 3
WHEN e.phq_rank between 61 and 80 THEN 4
ELSE 5
END as rank_level
FROM predicthq.predicthq_events_retail_london e
RIGHT JOIN saved_locations_daily s
ON ST_DISTANCE(e.geo, ST_MAKEPOINT(s.lon, s.lat)) <=
CASE
WHEN s.radius_unit = 'mi' THEN s.radius * 1609.34
WHEN s.radius_unit = 'km' THEN s.radius * 1000
END
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_start)) <= s.date
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_end)) >= s.date
WHERE e.phq_rank IS NOT NULL
AND e.category in ('academic','public-holidays','school-holidays','observances')
),
events_ranked_distinct AS ( --Get count per rank level
SELECT
r.date,
r.location,
r.category,
r.academic_split,
r.rank_level,
count(DISTINCT r.event_id) AS distinct_event_count
FROM events_ranked r
GROUP BY
r.date,
r.location,
r.category,
r.academic_split,
r.rank_level
)
SELECT --Final formatting and select for phq_rank_features
data_range.date,
data_range.location,
SUM(CASE WHEN r.category = 'observances' AND r.rank_level = 1 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_observances_rank_level1,
SUM(CASE WHEN r.category = 'observances' AND r.rank_level = 2 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_observances_rank_level2,
SUM(CASE WHEN r.category = 'observances' AND r.rank_level = 3 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_observances_rank_level3,
SUM(CASE WHEN r.category = 'observances' AND r.rank_level = 4 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_observances_rank_level4,
SUM(CASE WHEN r.category = 'observances' AND r.rank_level = 5 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_observances_rank_level5,
SUM(CASE WHEN r.category = 'public-holidays' AND r.rank_level = 1 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_public_holidays_rank_level1,
SUM(CASE WHEN r.category = 'public-holidays' AND r.rank_level = 2 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_public_holidays_rank_level2,
SUM(CASE WHEN r.category = 'public-holidays' AND r.rank_level = 3 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_public_holidays_rank_level3,
SUM(CASE WHEN r.category = 'public-holidays' AND r.rank_level = 4 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_public_holidays_rank_level4,
SUM(CASE WHEN r.category = 'public-holidays' AND r.rank_level = 5 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_public_holidays_rank_level5,
SUM(CASE WHEN r.category = 'school-holidays' AND r.rank_level = 1 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_school_holidays_rank_level1,
SUM(CASE WHEN r.category = 'school-holidays' AND r.rank_level = 2 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_school_holidays_rank_level2,
SUM(CASE WHEN r.category = 'school-holidays' AND r.rank_level = 3 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_school_holidays_rank_level3,
SUM(CASE WHEN r.category = 'school-holidays' AND r.rank_level = 4 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_school_holidays_rank_level4,
SUM(CASE WHEN r.category = 'school-holidays' AND r.rank_level = 5 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_school_holidays_rank_level5,
SUM(CASE WHEN r.academic_split = 'session' AND r.rank_level = 1 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_session_rank_level1,
SUM(CASE WHEN r.academic_split = 'session' AND r.rank_level = 2 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_session_rank_level2,
SUM(CASE WHEN r.academic_split = 'session' AND r.rank_level = 3 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_session_rank_level3,
SUM(CASE WHEN r.academic_split = 'session' AND r.rank_level = 4 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_session_rank_level4,
SUM(CASE WHEN r.academic_split = 'session' AND r.rank_level = 5 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_session_rank_level5,
SUM(CASE WHEN r.academic_split = 'exam' AND r.rank_level = 1 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_exam_rank_level1,
SUM(CASE WHEN r.academic_split = 'exam' AND r.rank_level = 2 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_exam_rank_level2,
SUM(CASE WHEN r.academic_split = 'exam' AND r.rank_level = 3 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_exam_rank_level3,
SUM(CASE WHEN r.academic_split = 'exam' AND r.rank_level = 4 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_exam_rank_level4,
SUM(CASE WHEN r.academic_split = 'exam' AND r.rank_level = 5 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_exam_rank_level5,
SUM(CASE WHEN r.academic_split = 'holiday' AND r.rank_level = 1 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_holiday_rank_level1,
SUM(CASE WHEN r.academic_split = 'holiday' AND r.rank_level = 2 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_holiday_rank_level2,
SUM(CASE WHEN r.academic_split = 'holiday' AND r.rank_level = 3 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_holiday_rank_level3,
SUM(CASE WHEN r.academic_split = 'holiday' AND r.rank_level = 4 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_holiday_rank_level4,
SUM(CASE WHEN r.academic_split = 'holiday' AND r.rank_level = 5 THEN r.distinct_event_count ELSE 0 END) AS phq_rank_academic_holiday_rank_level5
FROM (SELECT date, location FROM saved_locations_daily) data_range
LEFT JOIN events_ranked_distinct r
ON data_range.date = r.date
AND data_range.location = r.location
GROUP BY data_range.date, data_range.location
ORDER BY data_range.date, data_range.location
;
SELECT * FROM phq_rank_features order by location, date;
```
{% endcode %}
### Predicted Impact features
The code calculates each value as the MAX of the Ranks of events occurring over each day, showing the highest rank Severe Weather event of each type occurring per day:
{% code title="Predicted Impact features" fullWidth="true" %}
```sql
----Predicted Impact features
CREATE OR REPLACE TEMP TABLE phq_impact_features as
WITH events_impact AS ( --Pull impact events within range
SELECT DISTINCT
e.event_id,
imp.value:date_local::DATE AS date,
s.location,
e.category,
imp.value:value::int AS phq_rank,
CASE
WHEN ARRAY_CONTAINS('blizzard'::variant, e.labels) THEN 'blizzard'
WHEN ARRAY_CONTAINS('snow'::variant, e.labels) THEN 'cold-wave-snow'
WHEN ARRAY_CONTAINS('cold-wave'::variant, e.labels) AND ARRAY_CONTAINS('storm'::variant, e.labels) THEN 'cold-wave-storm'
WHEN ARRAY_CONTAINS('cold-wave'::variant, e.labels) THEN 'cold-wave'
WHEN ARRAY_CONTAINS('air-quality'::variant, e.labels) OR ARRAY_CONTAINS('fog'::variant, e.labels) OR ARRAY_CONTAINS('sand'::variant, e.labels) THEN 'air-quality'
WHEN ARRAY_CONTAINS('thunderstorm'::variant, e.labels) THEN 'thunderstorm'
WHEN ARRAY_CONTAINS('tropical-storm'::variant, e.labels) THEN 'tropical-storm'
WHEN ARRAY_CONTAINS('tornado'::variant, e.labels) THEN 'tornado'
WHEN ARRAY_CONTAINS('hurricane'::variant, e.labels) OR ARRAY_CONTAINS('cyclone'::variant, e.labels) OR ARRAY_CONTAINS('typhoon'::variant, e.labels) THEN 'hurricane'
WHEN ARRAY_CONTAINS('dust'::variant, e.labels) AND ARRAY_CONTAINS('storm'::variant, e.labels) THEN 'dust-storm'
WHEN ARRAY_CONTAINS('dust'::variant, e.labels) THEN 'dust'
WHEN ARRAY_CONTAINS('rain'::variant, e.labels) OR ARRAY_CONTAINS('flood'::variant, e.labels) OR ARRAY_CONTAINS('rain'::variant, e.labels) THEN 'flood'
WHEN ARRAY_CONTAINS('heat-wave'::variant, e.labels) THEN 'heat-wave'
WHEN ARRAY_CONTAINS('wind'::variant, e.labels) OR ARRAY_CONTAINS('hazardous-surf'::variant, e.labels) OR ARRAY_CONTAINS('storm'::variant, e.labels) THEN 'dust-storm'
END AS weather_category
FROM predicthq.events_0 e
RIGHT JOIN saved_locations_daily s
ON ST_DISTANCE(e.geo, ST_MAKEPOINT(s.lon, s.lat)) <=
CASE
WHEN s.radius_unit = 'mi' THEN s.radius * 1609.34
WHEN s.radius_unit = 'km' THEN s.radius * 1000
END
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_start)) <= s.date
AND TO_DATE(CONVERT_TIMEZONE(CASE WHEN e.timezone IS NOT NULL THEN e.timezone ELSE 'UTC' END, e.event_end)) >= s.date,
latERAL FlatTEN(INPUT => IMPACT_PATTERNS) vert,
latERAL FlatTEN(INPUT => vert.value:impacts) imp
WHERE vert.value:vertical::STRING = 'retail'
AND e.category = 'severe-weather'
AND weather_category IS NOT NULL
)
SELECT --final formatting and select for phq_impact_features
data_range.date,
data_range.location,
IFNULL(MAX(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_air_quality_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'blizzard' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_blizzard_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'cold-wave' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_cold_wave_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'cold-wave-snow' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_cold_wave_snow_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'cold-wave-storm' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_cold_wave_storm_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'dust' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_dust_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'dust-storm' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_dust_storm_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'flood' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_flood_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'heat-wave' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_heat_wave_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'hurricane' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_hurricane_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'thunderstorm' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_thunderstorm_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'tornado' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_tornado_retail,
IFNULL(MAX(CASE WHEN i.weather_category = 'tropical-storm' THEN i.phq_rank ELSE NULL END),0) AS phq_impact_severe_weather_tropical_storm_retail
FROM (SELECT date, location FROM saved_locations_daily) data_range
LEFT JOIN events_impact i
ON data_range.date = i.date
AND data_range.location = i.location
GROUP BY data_range.date, data_range.location
ORDER BY data_range.date, data_range.location
;
SELECT * FROM phq_impact_features order by location, date;
```
{% endcode %}
If you want metrics other than MAX, use the following code as a template for each column. The weather\_category name part of the code (in these examples defaulted to ‘air-quality’) changes depending on which feature you want to call. Refer to the earlier column code for the available weather\_category features:
{% code title="Count" fullWidth="true" %}
```sql
COUNT(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE NULL END)
```
{% endcode %}
{% code title="Average" fullWidth="true" %}
```sql
IFNULL(ROUND(AVG(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE NULL END),2),0)
```
{% endcode %}
{% code title="Median" fullWidth="true" %}
```sql
IFNULL(ROUND(MEDIAN(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE NULL END),2),0)
```
{% endcode %}
{% code title="Standard Deviation" fullWidth="true" %}
```sql
IFNULL(ROUND(STDDEV(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE NULL END),2),0)
```
{% endcode %}
{% code title="Min" fullWidth="true" %}
```sql
IFNULL(MIN(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE NULL END),0)
```
{% endcode %}
{% code title="SUM" fullWidth="true" %}
```sql
SUM(CASE WHEN i.weather_category = 'air-quality' THEN i.phq_rank ELSE 0 END)
```
{% endcode %}
## Step 3: Final select for all features
The following code pulls all the features generated earlier into a single table called **ML\_FEATURES\_FOR\_LOCATIONS**.\
\
Machine Learning models can use this output directly. If unsure what features to use, create a Beam Analysis for the locations. To select features, use the Feature Importance results - see [ML features by location notebook](https://github.com/predicthq/phq-data-science-docs/blob/master/demand-forecasting-with-events/identify-location-level-features-with-beam-api.ipynb). To view all features in one table, use the following code:
{% code title="Combined Table" fullWidth="true" %}
```sql
----Combine Select for viewing all in one
CREATE OR REPLACE TEMP TABLE ml_features_for_locations AS
SELECT
a.date,
a.location,
a.phq_attendance_community, --Attendance columns
a.phq_attendance_concerts,
a.phq_attendance_conferences,
a.phq_attendance_expos,
a.phq_attendance_festivals,
a.phq_attendance_performing_arts,
a.phq_attendance_sports,
a.phq_attendance_school_holidays,
a.phq_attendance_academic_graduation,
a.phq_attendance_academic_social,
r.phq_rank_observances_rank_level1, --Rank Level columns
r.phq_rank_observances_rank_level2,
r.phq_rank_observances_rank_level3,
r.phq_rank_observances_rank_level4,
r.phq_rank_observances_rank_level5,
r.phq_rank_public_holidays_rank_level1,
r.phq_rank_public_holidays_rank_level2,
r.phq_rank_public_holidays_rank_level3,
r.phq_rank_public_holidays_rank_level4,
r.phq_rank_school_holidays_rank_level5,
r.phq_rank_academic_session_rank_level1,
r.phq_rank_academic_session_rank_level2,
r.phq_rank_academic_session_rank_level3,
r.phq_rank_academic_session_rank_level4,
r.phq_rank_academic_session_rank_level5,
r.phq_rank_academic_exam_rank_level1,
r.phq_rank_academic_exam_rank_level2,
r.phq_rank_academic_exam_rank_level3,
r.phq_rank_academic_exam_rank_level4,
r.phq_rank_academic_exam_rank_level5,
r.phq_rank_academic_holiday_rank_level1,
r.phq_rank_academic_holiday_rank_level2,
r.phq_rank_academic_holiday_rank_level3,
r.phq_rank_academic_holiday_rank_level4,
r.phq_rank_academic_holiday_rank_level5,
i.phq_impact_severe_weather_air_quality_retail, --Impact columns
i.phq_impact_severe_weather_blizzard_retail,
i.phq_impact_severe_weather_cold_wave_retail,
i.phq_impact_severe_weather_cold_wave_snow_retail,
i.phq_impact_severe_weather_cold_wave_storm_retail,
i.phq_impact_severe_weather_dust_retail,
i.phq_impact_severe_weather_dust_storm_retail,
i.phq_impact_severe_weather_flood_retail,
i.phq_impact_severe_weather_heat_wave_retail,
i.phq_impact_severe_weather_hurricane_retail,
i.phq_impact_severe_weather_thunderstorm_retail,
i.phq_impact_severe_weather_tornado_retail,
i.phq_impact_severe_weather_tropical_storm_retail
FROM phq_attendance_features a
JOIN phq_rank_features r
ON a.date = r.date
AND a.location = r.location
JOIN phq_impact_features i
ON a.date = i.date
AND a.location = i.location
;
SELECT * FROM ml_features_for_locations ORDER BY location, date;
```
{% endcode %}
The output table should look like this (Note: the example here shows only the first three columns):
| DATE | LOCATION | PHQ_ATTENDANCE_COMMUNITY | PHQ_ATTENDANCE_CONCERTS | PHQ_ATTENDANCE_CONFERENCES |
|---|
| 2024-01-01 | Hyde Park | 68 | 1,839 | 1,578 |
| 2024-01-02 | Hyde Park | 0 | 469 | 126 |
| 2024-01-03 | Hyde Park | 200 | 346 | 139 |
| 2024-01-04 | Hyde Park | 0 | 2,029 | 324 |
| 2024-01-05 | Hyde Park | 120 | 691 | 238 |
### Refer back to [main guide](/integrations/third-party-integrations/snowflake/snowflake-data-science-guide.md)
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.
Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:
```
GET https://docs.predicthq.com/integrations/third-party-integrations/snowflake/snowflake-data-science-guide/sql-method-guide.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.