-
Notifications
You must be signed in to change notification settings - Fork 0
/
durationBins.sql
124 lines (112 loc) · 7.34 KB
/
durationBins.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE TEMP TABLE ga_duration_bins AS
select origin_country, "durationBin", sum("Users") "Users", sum("Sessions") "Sessions"
FROM
(select ga_city_sample.*, ga_fb_geocoded_cities.distance_km, ga_fb_geocoded_cities.duration_min,
CASE
WHEN ga_fb_geocoded_cities.duration_min <= 60 then 'a Within 60 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 61 and 120 then 'b Within 61 to 120 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 121 and 180 then 'c Within 121 to 180'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 181 and 240 then 'd Within 181 to 240'
WHEN ga_fb_geocoded_cities.duration_min > 240 THEN 'e Over 240 min'
ELSE null
END "durationBin"
from ga_city_sample, ga_fb_geocoded_cities
WHERE ga_city_sample.origin_city || ', ' || ga_city_sample.origin_country = ga_fb_geocoded_cities.adr
AND ga_city_sample.origin_city <> '(not set)'
AND ga_city_sample.origin_city is not null
AND distance_km is not null
AND REGEXP_REPLACE(ga_city_sample.origin_city,'[[:digit:]]','','g') <> ''
AND ga_fb_geocoded_cities.city_name <> ga_fb_geocoded_cities.country_name
) ga
GROUP BY origin_country, "durationBin";
CREATE TEMP TABLE ga_duration_bins_share AS
WITH country_totals AS (SELECT origin_country, SUM("Users") "Users", SUM("Sessions") "Sessions" FROM ga_duration_bins GROUP BY origin_country)
SELECT ga_duration_bins.*, round(((ga_duration_bins."Users"/country_totals."Users")::numeric*100), 2) pct_share_users,
round(((ga_duration_bins."Sessions"/country_totals."Sessions")::numeric*100), 2) pct_share_sessions
FROM ga_duration_bins, country_totals
WHERE ga_duration_bins.origin_country = country_totals.origin_country;
CREATE TEMP TABLE fb_duration_bins AS
select origin_country, "durationBin", sum("Impressions") "Impressions", sum("Content Activity") "Content Activity"
FROM
(select fb_city_sample.*, ga_fb_geocoded_cities.distance_km, ga_fb_geocoded_cities.duration_min,
CASE
WHEN ga_fb_geocoded_cities.duration_min <= 60 then 'a Within 60 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 61 and 120 then 'b Within 61 to 120 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 121 and 180 then 'c Within 121 to 180'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 181 and 240 then 'd Within 181 to 240'
WHEN ga_fb_geocoded_cities.duration_min > 240 THEN 'e Over 240 min'
ELSE null
END "durationBin"
from fb_city_sample, ga_fb_geocoded_cities
WHERE fb_city_sample.origin_city || ', ' || fb_city_sample.origin_country = ga_fb_geocoded_cities.adr
AND fb_city_sample.origin_city <> '(not set)'
AND fb_city_sample.origin_city is not null
AND distance_km is not null
AND REGEXP_REPLACE(fb_city_sample.origin_city,'[[:digit:]]','','g') <> ''
AND ga_fb_geocoded_cities.city_name <> ga_fb_geocoded_cities.country_name
) ga
GROUP BY origin_country, "durationBin";
CREATE TEMP TABLE fb_duration_bins_share AS
WITH country_totals AS (SELECT origin_country, SUM("Impressions") "Impressions", SUM("Content Activity") "Content Activity" FROM fb_duration_bins GROUP BY origin_country)
SELECT fb_duration_bins.*, round(((fb_duration_bins."Impressions"/country_totals."Impressions")::numeric*100), 2) pct_share_impressions,
round(((fb_duration_bins."Content Activity"/country_totals."Content Activity")::numeric*100), 2) pct_share_content_activity
FROM fb_duration_bins, country_totals
WHERE fb_duration_bins.origin_country = country_totals.origin_country;
--MONTHLY GOOGLE ANALYTICS SAMPLE
CREATE TEMP TABLE ga_monthly_duration_bins AS
select origin_country, month_unified, "durationBin", sum("Users") "Users", sum("Sessions") "Sessions"
FROM
(select ga_city_sample.*, ga_fb_geocoded_cities.distance_km, ga_fb_geocoded_cities.duration_min,
CASE
WHEN ga_fb_geocoded_cities.duration_min <= 60 then 'a Within 60 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 61 and 120 then 'b Within 61 to 120 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 121 and 180 then 'c Within 121 to 180'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 181 and 240 then 'd Within 181 to 240'
WHEN ga_fb_geocoded_cities.duration_min > 240 THEN 'e Over 240 min'
ELSE null
END "durationBin"
from ga_city_sample, ga_fb_geocoded_cities
WHERE ga_city_sample.origin_city || ', ' || ga_city_sample.origin_country = ga_fb_geocoded_cities.adr
AND ga_city_sample.origin_city <> '(not set)'
AND ga_city_sample.origin_city is not null
AND distance_km is not null
AND REGEXP_REPLACE(ga_city_sample.origin_city,'[[:digit:]]','','g') <> ''
AND ga_fb_geocoded_cities.city_name <> ga_fb_geocoded_cities.country_name
) ga
GROUP BY origin_country, month_unified, "durationBin"
ORDER BY origin_country, month_unified, "durationBin";
CREATE TEMP TABLE ga_monthly_duration_bins_share AS
WITH country_totals AS (SELECT origin_country, month_unified, SUM("Users") "Users", SUM("Sessions") "Sessions" FROM ga_monthly_duration_bins GROUP BY origin_country, month_unified)
SELECT ga_monthly_duration_bins.*, round(((ga_monthly_duration_bins."Users"/country_totals."Users")::numeric*100), 2) pct_share_users,
round(((ga_monthly_duration_bins."Sessions"/country_totals."Sessions")::numeric*100), 2) pct_share_sessions
FROM ga_monthly_duration_bins, country_totals
WHERE ga_monthly_duration_bins.origin_country = country_totals.origin_country AND ga_monthly_duration_bins.month_unified = country_totals.month_unified;
CREATE TEMP TABLE fb_monthly_duration_bins AS
select origin_country, month_unified, "durationBin", sum("Impressions") "Impressions", sum("Content Activity") "Content Activity"
FROM
(select fb_city_sample.*, ga_fb_geocoded_cities.distance_km, ga_fb_geocoded_cities.duration_min,
CASE
WHEN ga_fb_geocoded_cities.duration_min <= 60 then 'a Within 60 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 61 and 120 then 'b Within 61 to 120 min'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 121 and 180 then 'c Within 121 to 180'
WHEN ga_fb_geocoded_cities.duration_min BETWEEN 181 and 240 then 'd Within 181 to 240'
WHEN ga_fb_geocoded_cities.duration_min > 240 THEN 'e Over 240 min'
ELSE null
END "durationBin"
from fb_city_sample, ga_fb_geocoded_cities
WHERE fb_city_sample.origin_city || ', ' || fb_city_sample.origin_country = ga_fb_geocoded_cities.adr
AND fb_city_sample.origin_city <> '(not set)'
AND fb_city_sample.origin_city is not null
AND distance_km is not null
AND REGEXP_REPLACE(fb_city_sample.origin_city,'[[:digit:]]','','g') <> ''
AND ga_fb_geocoded_cities.city_name <> ga_fb_geocoded_cities.country_name
) ga
GROUP BY origin_country, month_unified, "durationBin"
ORDER BY origin_country, month_unified, "durationBin";
CREATE TEMP TABLE fb_monthly_duration_bins_share AS
WITH country_totals AS (SELECT origin_country, month_unified, SUM("Impressions") "Impressions", SUM("Content Activity") "Content Activity" FROM fb_monthly_duration_bins GROUP BY origin_country, month_unified)
SELECT fb_monthly_duration_bins.*, round(((fb_monthly_duration_bins."Impressions"/country_totals."Impressions")::numeric*100), 2) pct_share_impressions,
round(((fb_monthly_duration_bins."Content Activity"/country_totals."Content Activity")::numeric*100), 2) pct_share_content_activity
FROM fb_monthly_duration_bins, country_totals
WHERE fb_monthly_duration_bins.origin_country = country_totals.origin_country AND fb_monthly_duration_bins.month_unified = country_totals.month_unified
ORDER BY origin_country, month_unified, "durationBin";