-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpartitionAndLag.sql
169 lines (165 loc) · 6.29 KB
/
partitionAndLag.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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
SELECT *
FROM ( $base_dates cal
LEFT JOIN
(SELECT partition_date,
count(opt_out_id) AS total_opt_out
FROM
(SELECT *
FROM
(SELECT base.opt_out_id ,
chirp_email_prefs,
partition_date ,
previous_email_prefs ,
CASE WHEN previous_email_prefs IS TRUE
AND chirp_email_prefs IS FALSE THEN 1 ELSE 0 END AS optout
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
link_date ,
partition_date ,
lag(chirp_email_prefs) over (partition BY opt_out_id
ORDER BY partition_date) AS previous_email_prefs
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
date(cast(substr(cast(a._partition_date AS string),0,4) AS int64), cast(substr(cast(a._partition_date AS string),5,2) AS int64) , cast(substr(cast(a._partition_date AS string),7,2) AS int64)) AS partition_date ,
date(TIMESTAMP_trunc(timestamp_millis(b.time_linked_with_user_ms),DAY,"America/Los_Angeles")) AS link_date
FROM lrs.history.email_preferences.all a,
unnest(a.device_data) b
JOIN lrs.devices v ON b.device_id = v.device_id
WHERE $base_filter
GROUP BY 1,
2,
3,
4)) base)
WHERE optout=1
AND opt_out_id IN
(SELECT opt_out_id
FROM lrs.history.email_preferences.yesterday
WHERE chirp_email_prefs = FALSE
GROUP BY 1))
GROUP BY 1 ) total_opt_out ON total_opt_out.partition_date = cal.cal_date
LEFT JOIN
( SELECT partition_date,
count(opt_out_id) AS email_opt_out
FROM
(SELECT *
FROM
(SELECT base.opt_out_id ,
chirp_email_prefs,
partition_date ,
previous_email_prefs ,
CASE WHEN previous_email_prefs IS TRUE
AND chirp_email_prefs IS FALSE THEN 1 ELSE 0 END AS optout
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
link_date ,
partition_date ,
lag(chirp_email_prefs) over (partition BY opt_out_id
ORDER BY partition_date) AS previous_email_prefs
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
date(cast(substr(cast(a._partition_date AS string),0,4) AS int64), cast(substr(cast(a._partition_date AS string),5,2) AS int64) , cast(substr(cast(a._partition_date AS string),7,2) AS int64)) AS partition_date ,
date(TIMESTAMP_trunc(timestamp_millis(b.time_linked_with_user_ms),DAY,"America/Los_Angeles")) AS link_date
FROM lrs.history.email_preferences.all a,
unnest(a.device_data) b
JOIN lrs.devices v ON b.device_id = v.device_id
WHERE $base_filter
GROUP BY 1,
2,
3,
4)) base)
WHERE optout=1
AND opt_out_id IN
(SELECT opt_out_id
FROM lrs.history.email_preferences.yesterday
WHERE chirp_email_prefs = FALSE
GROUP BY 1)
AND opt_out_id IN
(SELECT signal_1
FROM lrs.healthcheck_OptOutMessages a
JOIN lrs.healthcheck_SentMessages b ON a.sent_message_id = b.sent_message_id))
GROUP BY 1 ) email_opt ON email_opt.partition_date = cal.cal_date
LEFT JOIN
( SELECT partition_date,
count(opt_out_id) AS onboarding_opt_out
FROM
(SELECT *
FROM
(SELECT base.opt_out_id ,
chirp_email_prefs,
partition_date ,
previous_email_prefs ,
CASE WHEN previous_email_prefs IS TRUE
AND chirp_email_prefs IS FALSE THEN 1 ELSE 0 END AS optout
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
link_date ,
partition_date ,
lag(chirp_email_prefs) over (partition BY opt_out_id
ORDER BY partition_date) AS previous_email_prefs
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
date(cast(substr(cast(a._partition_date AS string),0,4) AS int64), cast(substr(cast(a._partition_date AS string),5,2) AS int64) , cast(substr(cast(a._partition_date AS string),7,2) AS int64)) AS partition_date ,
date(TIMESTAMP_trunc(timestamp_millis(b.time_linked_with_user_ms),DAY,"America/Los_Angeles")) AS link_date
FROM lrs.history.email_preferences.all a,
unnest(a.device_data) b
JOIN lrs.devices v ON b.device_id = v.device_id
WHERE $base_filter -- and cast(a._partition_date as int64) between 20161110 and 20161129
GROUP BY 1,
2,
3,
4)) base)
WHERE optout=1
AND opt_out_id IN
(SELECT opt_out_id
FROM lrs.history.email_preferences.yesterday
WHERE chirp_email_prefs = FALSE
GROUP BY 1)
AND opt_out_id IN
(SELECT signal_1
FROM lrs.healthcheck_OptOutMessages a
JOIN lrs.healthcheck_SentMessages b ON a.sent_message_id = b.sent_message_id)
AND opt_out_id IN
(SELECT signal_1
FROM $id_email(OptOutMessages)
WHERE a.campaign_id IN ($onboarding_ids)))
GROUP BY 1 ) onboarding_opt ON onboarding_opt.partition_date = cal.cal_date
LEFT JOIN
( SELECT current_sub_date,
CASE WHEN current_sub_date ='2016-11-10' THEN subscribers ELSE subscribers - lag(subscribers) over (
ORDER BY current_sub_date) END subscribers
FROM
( SELECT partition_date AS current_sub_date ,
count(opt_out_id) subscribers
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
partition_date ,
previous_email_prefs
FROM
(SELECT opt_out_id ,
chirp_email_prefs ,
link_date ,
partition_date ,
lag(chirp_email_prefs) over (partition BY opt_out_id
ORDER BY partition_date) AS previous_email_prefs
FROM
( SELECT opt_out_id ,
chirp_email_prefs ,
date(cast(substr(cast(a._partition_date AS string),0,4) AS int64), cast(substr(cast(a._partition_date AS string),5,2) AS int64) , cast(substr(cast(a._partition_date AS string),7,2) AS int64)) AS partition_date ,
date(TIMESTAMP_trunc(timestamp_millis(b.time_linked_with_user_ms),DAY,"America/Los_Angeles")) AS link_date
FROM lrs.history.email_preferences.all a,
unnest(a.device_data) b
JOIN lrs.devices v ON b.device_id = v.device_id
WHERE $base_filter
GROUP BY 1,
2,
3,
4)))
WHERE chirp_email_prefs = TRUE
GROUP BY 1 )) b ON b.current_sub_date = cal.cal_date)