-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathsuspensiones
199 lines (191 loc) · 7.31 KB
/
suspensiones
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
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
SELECT DISTINCT
a.dt
,a.country_code
,a.shop_id
,a.shop_name
,a.is_suspend
,b.last_online_date
,IF(
c.suspended_start_date IS NOT NULL
,'Permanent Suspended by CS'
,IF(
d.dormant_start_date IS NOT NULL
,'Dormant'
,IF(
e.tempoff_start_date IS NOT NULL
,'Temporary Offline'
,IF(
f.service_start_date IS NOT NULL
,'Service Suspension'
,'Not Suspended'
)
)
)
) AS suspension_status
,c.suspended_start_date
,d.dormant_start_date
,d.dormant_end_date
,e.tempoff_start_date
,e.tempoff_end_date
,f.service_start_date
FROM (
SELECT
concat_ws('-', year, month, day) AS dt
,country_code
,is_suspend
,shop_id
,shop_name
FROM soda_international_dwm.dwm_bizopp_wide_d_whole
WHERE concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX','CO','CR','PE')
--AND shop_id IN (5764607669625947123,5764607710042260538,5764607695513190469)
) AS a
LEFT JOIN (
SELECT
concat_ws('-', year, month, day) AS dt
,shop_id
,TO_DATE(last_online_date) AS last_online_date
FROM soda_international_dwm.dwm_shop_first_last_time_d_whole
WHERE concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX','CO','CR','PE')
AND last_online_date IS NOT NULL
AND last_online_date <> ''
) AS b
ON a.shop_id = b.shop_id
LEFT JOIN (
SELECT
country_code
,entity_id as shop_id
,MAX(TO_DATE(create_ts_local)) AS suspended_start_date
FROM soda_international_dwd.dwd_soda_b_operation_operation_d_increment
WHERE operation_type = '30000'
AND create_ts_local IS NOT NULL
AND create_ts_local <> ''
--AND entity_id = 5764607669625947123
GROUP BY country_code, entity_id
) AS c
ON a.shop_id = c.shop_id
AND a.is_suspend = 1
AND b.last_online_date <= c.suspended_start_date
LEFT JOIN (
SELECT DISTINCT
a.shop_id
,IF(
a.dormant_start_date IS NULL
,TO_DATE(a.modify_time)
,a.dormant_start_date
) AS dormant_start_date
,IF (
a.dormant_end_date IS NULL
,'${end_date}'
,a.dormant_end_date
) AS dormant_end_date
,a.tag_change
,a.modify_time
,a.rank_
FROM (
SELECT DISTINCT
a.shop_id
,a.dormant_start_date
,a.dormant_end_date
,a.tag_change
,a.modify_time
,ROW_NUMBER() OVER (PARTITION BY a.shop_id ORDER BY a.modify_time DESC,a.dormant_start_date DESC,a.country ASC) AS rank_
FROM (
SELECT DISTINCT
country_code AS country
,shop_id
,TO_DATE(regexp_replace(get_json_object(get_json_object(content, '$.effect_time'), '$[*].start_time'), '"', ''), 'yyyy/MM/dd') AS dormant_start_date
,TO_DATE(regexp_replace(get_json_object(get_json_object(content, '$.effect_time'), '$[*].end_time'), '"', ''), 'yyyy/MM/dd') AS dormant_end_date
,IF(get_json_object(content, '$.operation_type') = 1,'Adding Tag','Erasing Tag') AS tag_change
--,bizopp_batch_job_detail_modify_time_bj AS modify_time
,from_utc_timestamp(
to_utc_timestamp(bizopp_batch_job_detail_modify_time_bj, 'Asia/Shanghai'),
'America/Bogota'
) AS modify_time
FROM soda_international_dwd.dwd_operation_bizopp_batch_job_d_increment
WHERE LOWER(get_json_object(content, '$.tag_type')) LIKE '%dormant%'
--AND shop_id = 5764607526470156359
--AND TO_DATE(regexp_replace(get_json_object(get_json_object(content, '$.effect_time'), '$[*].start_time'), '"', ''), 'yyyy/MM/dd') IS NULL
) AS a
) AS a
WHERE a.rank_ = 1 AND a.tag_change = 'Adding Tag'
) AS d
ON a.shop_id = d.shop_id
AND b.last_online_date <= d.dormant_start_date
AND a.dt BETWEEN d.dormant_start_date AND d.dormant_end_date
LEFT JOIN (
SELECT DISTINCT
a.shop_id
,IF(
a.tempoff_start_date IS NULL
,TO_DATE(a.modify_time)
,a.tempoff_start_date
) AS tempoff_start_date
,IF (
a.tempoff_end_date IS NULL
,'${end_date}'
,a.tempoff_end_date
) AS tempoff_end_date
,a.tag_change
,a.modify_time
,a.rank_
FROM (
SELECT DISTINCT
a.shop_id
,a.tempoff_start_date
,a.tempoff_end_date
,a.tag_change
,a.modify_time
,ROW_NUMBER() OVER (PARTITION BY a.shop_id ORDER BY a.modify_time DESC,a.tempoff_start_date DESC,a.country ASC) AS rank_
FROM (
SELECT DISTINCT
country_code AS country
,shop_id
,TO_DATE(regexp_replace(get_json_object(get_json_object(content, '$.effect_time'), '$[*].start_time'), '"', ''), 'yyyy/MM/dd') AS tempoff_start_date
,TO_DATE(regexp_replace(get_json_object(get_json_object(content, '$.effect_time'), '$[*].end_time'), '"', ''), 'yyyy/MM/dd') AS tempoff_end_date
,IF(get_json_object(content, '$.operation_type') = 1,'Adding Tag','Erasing Tag') AS tag_change
--,bizopp_batch_job_detail_modify_time_bj AS modify_time
,from_utc_timestamp(
to_utc_timestamp(bizopp_batch_job_detail_modify_time_bj, 'Asia/Shanghai'),
'America/Bogota'
) AS modify_time
FROM soda_international_dwd.dwd_operation_bizopp_batch_job_d_increment
WHERE LOWER(get_json_object(content, '$.tag_type')) LIKE '%temporary offline%'
--AND shop_id = 5764607526470156359
--AND TO_DATE(regexp_replace(get_json_object(get_json_object(content, '$.effect_time'), '$[*].start_time'), '"', ''), 'yyyy/MM/dd') IS NULL
) AS a
) AS a
WHERE a.rank_ = 1 AND a.tag_change = 'Adding Tag'
) AS e
ON a.shop_id = e.shop_id
AND b.last_online_date <= e.tempoff_start_date
AND a.dt BETWEEN e.tempoff_start_date AND e.tempoff_end_date
LEFT JOIN (
SELECT
country_code
,entity_id AS shop_id
,TO_DATE(create_ts_local) AS service_start_date
FROM soda_international_dwd.dwd_soda_b_operation_operation_d_increment
WHERE operation_type = '40011'
AND country_code IN ('MX','CO','CR','PE')
AND TO_DATE(create_ts_local) = '${end_date}'
) AS f
ON a.shop_id = f.shop_id
WHERE IF(
c.suspended_start_date IS NOT NULL
,'Permanent Suspended by CS'
,IF(
d.dormant_start_date IS NOT NULL
,'Dormant'
,IF(
e.tempoff_start_date IS NOT NULL
,'Temporary Offline'
,IF(
f.service_start_date IS NOT NULL
,'Service Suspension'
,'Not Suspended'
)
)
)
) <> 'Not Suspended'