Skip to content

Latest commit

 

History

History
80 lines (63 loc) · 6.31 KB

README.md

File metadata and controls

80 lines (63 loc) · 6.31 KB

safegraph_snowflake_demo

These are SQL scripts for a demo of SafeGraph's Patterns data in Snowflake

Once you have the Starbucks SafeGraph Sample and the SafeGraph Open Census Data as SHARES in Snowflake, execute the scripts in the following order:

1_FindTwoStarbucksByDwellTime

PLACEKEY STREET_ADDRESS CITY REGION DISTANCE_FROM_HOME POSTAL_CODE RAW_VISIT_COUNTS pct_<5 pct_5-10 pct_11-20 pct_21-60 pct_61-120 pct_121-240 pct_>240
222-224@5z5-3t2-3wk 1500 Duarte Rd Duarte CA 31793 91010 1672 0.8373205742 5.741626794 8.971291866 20.633971292 20.873205742 19.078947368 23.863636364
22h-222@5z5-3qr-psq 1120 W 6th St # 102 Los Angeles CA 3073 90017 1616 1.670792079 15.903465347 13.551980198 18.254950495 11.757425743 10.51980198 28.341584158
zzw-222@5z6-3r6-59f 1437 E Gage Ave Los Angeles CA 2621 90001 1121 1.873327386 23.193577163 14.629794826 21.855486173 9.545049063 9.366636931 19.536128457
zzw-225@5z4-mws-835 7855 Kew Ave Rancho Cucamonga CA 11119 91739 1111 1.530153015 11.97119712 15.121512151 38.883888389 13.321332133 9.270927093 9.900990099
zzy-226@5vh-rxn-vj9 3401 Dale Road 244 Vintage Faire Mall Modesto CA 18242 95356 1556 3.084832905 17.030848329 20.179948586 34.061696658 10.218508997 8.547557841 6.876606684
...

2_LearnMoreAboutTheTwoStarbucks

PLACEKEY LOCATION_NAME STREET_ADDRESS CITY REGION POSTAL_CODE POI_CBG RAW_VISIT_COUNTS RAW_VISITOR_COUNTS DISTANCE_FROM_HOME MEDIAN_DWELL POPULARITY_BY_HOUR
222-224@5z5-3t2-3wk Starbucks 1500 Duarte Rd Duarte CA 91010 060374301021 1672 489 31793 98 [ 46, 36, 34, 35, 35, 59, 133, 286, 471, 583, 598, 566, 576, 592, 578, 476, 349, 277, 190, 144, 88, 63, 60, 55 ]
zzw-223@5z4-rcm-4gk Starbucks 2734 N Tustin St Orange CA 92865 060590762023 1039 705 4765 9 [ 4, 3, 2, 2, 10, 19, 48, 57, 56, 89, 75, 85, 96, 98, 120, 88, 75, 56, 78, 73, 60, 44, 24, 3 ]

3_HourlyVisitRate

PLACEKEY LOCATION_NAME CITY H0 H1 H2 H3 H4 H5 H6 H7 H8 H9 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23
zzw-223@5z4-rcm-4gk Starbucks Orange 4 3 2 2 10 19 48 57 56 89 75 85 96 98 120 88 75 56 78 73 60 44 24 3
222-224@5z5-3t2-3wk Starbucks Duarte 46 36 34 35 35 59 133 286 471 583 598 566 576 592 578 476 349 277 190 144 88 63 60 55

When plotted, the hourly data should look like this:

image

import matplotlib.pyplot as plt
duarte = [ 46, 36, 34, 35, 35, 59, 133, 286, 471, 583, 598, 566, 576, 592, 578, 476, 349, 277, 190, 144, 88, 63, 60, 55 ]
orange = [ 4, 3, 2, 2, 10, 19, 48, 57, 56, 89, 75, 85, 96, 98, 120, 88, 75, 56, 78, 73, 60, 44, 24, 3 ]
plt.plot(range(24),[x/sum(duarte) for x in duarte],label='Duarte')
plt.plot(range(24),[x/sum(orange) for x in orange],label='Orange')
plt.xlabel('Hour of day')
plt.ylabel('Normalized popularity_by_hour')
plt.legend()
plt.show()

4_LookUpCensusIncomeColumns

TABLE_ID FIELD_FULL_NAME FIELD_LEVEL_1 FIELD_LEVEL_2 FIELD_LEVEL_3
B19001e1 HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2016 INFLATION-ADJUSTED DOLLARS): Total: Households -- (Estimate) Household Income In The Past 12 Months (In 2016 Inflation-Adjusted Dollars) Total Households -- (Estimate)
B19001e10 HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2016 INFLATION-ADJUSTED DOLLARS): $45,000 to $49,999: Households -- (Estimate) Household Income In The Past 12 Months (In 2016 Inflation-Adjusted Dollars) $45,000 To $49,999 Households -- (Estimate)
B19001e11 HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2016 INFLATION-ADJUSTED DOLLARS): $50,000 to $59,999: Households -- (Estimate) Household Income In The Past 12 Months (In 2016 Inflation-Adjusted Dollars) $50,000 To $59,999 Households -- (Estimate)
B19001e12 HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2016 INFLATION-ADJUSTED DOLLARS): $60,000 to $74,999: Households -- (Estimate) Household Income In The Past 12 Months (In 2016 Inflation-Adjusted Dollars) $60,000 To $74,999 Households -- (Estimate)
B19001e13 HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2016 INFLATION-ADJUSTED DOLLARS): $75,000 to $99,999: Households -- (Estimate) Household Income In The Past 12 Months (In 2016 Inflation-Adjusted Dollars) $75,000 To $99,999 Households -- (Estimate)
...

5_StarbucksNeighborhoodIncome

PLACEKEY LOCATION_NAME CITY POI_CBG MEDIAN_HH_INCOME HH_INCOME_LT_60K HH_INCOME_60K_TO_100K HH_INCOME_GTE_100K
222-224@5z5-3t2-3wk Starbucks Duarte 060374301021 57838 285 115 106
zzw-223@5z4-rcm-4gk Starbucks Orange 060590762023 97308 197 142 337

6_StarbucksVisitorHomeIncome

PLACEKEY LOCATION_NAME CITY VISITOR_HOME_CBG VISITOR_COUNT HH_MEDIAN_INCOME
222-224@5z5-3t2-3wk Starbucks Duarte 060371066452 4 114205
222-224@5z5-3t2-3wk Starbucks Duarte 060371212101 4 45109
222-224@5z5-3t2-3wk Starbucks Duarte 060371243002 4 51133
222-224@5z5-3t2-3wk Starbucks Duarte 060371278063 4 44071
222-224@5z5-3t2-3wk Starbucks Duarte 060371398021 4 139500
...

7_StarbucksWeightedAverageVisitorIncome

PLACEKEY LOCATION_NAME CITY WEIGHTED_VISITOR_MEDIAN_INCOME
zzw-223@5z4-rcm-4gk Starbucks Orange 96152.1875
222-224@5z5-3t2-3wk Starbucks Duarte 78993.694087404