Open SPTKL opened 3 years ago
WITH dataset AS(
SELECT date_start, safegraph_place_id, poi_cbg, map_keys(a) as origins, a
FROM (
SELECT
safegraph_place_id,
poi_cbg,
CAST(SUBSTR(date_range_start, 1, 10) AS DATE) as date_start,
CAST(json_parse(visitor_home_cbgs) as map<varchar, varchar>) as a
FROM safegraph.weekly_patterns
WHERE SUBSTR(poi_cbg,1,5) IN ('36085','36081','36061','36047','36005')
AND CAST('2020-10-01' AS DATE) < dt
AND CAST('2020-10-20' AS DATE) > dt
) b
)
SELECT
safegraph_place_id,
CAST(EXTRACT(year from date_start) AS VARCHAR)||'-'||LPAD(CAST(EXTRACT(week from date_start) AS VARCHAR),2,'0') as year_week,
poi_cbg,
visitor_home_cbg,
CAST(a[visitor_home_cbg] AS SMALLINT) as visits
FROM dataset
CROSS JOIN unnest(origins) t(visitor_home_cbg)
WITH daily_visits AS(
SELECT safegraph_place_id, poi_cbg, date_add('day', row_number() over(partition by safegraph_place_id), date_start) AS date_current, CAST(visits AS SMALLINT) as visits
FROM (
SELECT
safegraph_place_id,
poi_cbg,
CAST(SUBSTR(date_range_start, 1, 10) AS DATE) as date_start,
CAST(SUBSTR(date_range_end, 1, 10) AS DATE) as date_end,
cast(json_parse(visits_by_day) as array<varchar>) as a
FROM safegraph.weekly_patterns
WHERE SUBSTR(poi_cbg,1,5) IN ('36085','36081','36061','36047','36005')
AND CAST('2020-10-01' AS DATE) < dt
AND CAST('2020-10-20' AS DATE) > dt
) b
CROSS JOIN UNNEST(a) as t(visits)
)
SELECT
safegraph_place_id,
CAST(EXTRACT(year from date_current) AS VARCHAR)||'-'||LPAD(CAST(EXTRACT(week from date_current) AS VARCHAR),2,'0') as year_week,
poi_cbg,
SUM(CASE WHEN EXTRACT(dow from date_current) NOT IN (0, 6) THEN visits END) as visits_weekday,
SUM(CASE WHEN EXTRACT(dow from date_current) IN (0, 6) THEN visits END) as visits_weekend,
SUM(visits) as visits_total
FROM daily_visits
GROUP BY EXTRACT(year from date_current),
EXTRACT(week from date_current),
safegraph_place_id,
poi_cbg
ORDER BY year_week, poi_cbg
Initial phase of implementation complete
Objective:
Proposed schema adding onto poi/weekly_nyc_poivisits
Data Loading
60
62
68
ETL Queries
POI Table with id, category, geom, square footage, zonename and zonecolor