· 3 years ago · Feb 11, 2022, 11:30 AM
1DROP TEMPORARY TABLE IF EXISTS adunits_to_disable;
2CREATE TEMPORARY TABLE adunits_to_disable
3SELECT DISTINCT sa.site_area_id, bsa.user_id, sa.`create_date`, bsa.adv_expense_gbp_30, bsa.requests_30 FROM rabota_db.site_area sa
4JOIN bi.site_area bsa ON bsa.site_area_id = sa.site_area_id
5LEFT JOIN rabota_db.attached_site_areas basa ON sa.site_area_id = basa.site_area_id # with attached site area
6LEFT JOIN bi.site_area asai ON asai.site_area_id = basa.attached_site_area_id # attached site areas info
7WHERE
8 (
9 (
10 basa.site_area_id IS NOT NULL # is base area
11 AND ((asai.adv_expense_gbp_30 = 0 #and have no attached areas with stats
12 AND asai.requests_30 = 0 )
13 OR asai.site_area_id IS NULL) # or have no attached areas
14
15 )
16 OR
17 (
18 basa.site_area_id IS NULL # is attached area itself
19 )
20 )
21AND bsa.adv_expense_gbp_30 = 0
22AND bsa.requests_30 = 0
23AND sa.`create_date` < ( NOW() - INTERVAL 3 MONTH )
24AND bsa.user_id IN (
25143718,
26130905,
27142171,
28143779,
29143174,
30111441,
31145613,
32145397,
33143885,
34103491,
35119997,
36142788,
37144892,
38145417,
39128837,
40116214,
41145592,
42141408,
43143924,
44114289,
45125676,
46143727,
47128317,
48117170,
49130221,
50127672,
51113821,
52122065,
53145832,
54118982,
55132210,
56130273,
57136691,
58122345,
59140542,
60129927,
61108350,
62128603,
63142114,
64142210,
65146294,
66125922,
67143448,
68114096,
69146466,
70123677,
71124567,
72127942,
73130955,
74134435,
75143263,
76144357,
77111604,
78140209,
79102556,
80129416,
81131176,
82141405,
83141406,
84145591,
85108589,
86141407,
87117333,
88122213,
89123741,
90126356,
91140846,
92143637,
93143764,
94144135,
95146308,
96116989,
97109931,
98111040,
99118339,
100120761,
101126239,
102131337,
103132502,
104134210,
105141900,
106143139,
107108973,
108113159,
109142135
110);
111
112UPDATE site_area sa
113JOIN adunits_to_disable dsa ON sa.`site_area_id` = dsa.`site_area_id`
114SET sa.active = 0,
115sa.modify_date = NOW() #,
116#sa.deleted = 1
117;
118