· 5 years ago · Jun 04, 2020, 06:52 PM
1SELECT
2 CASE
3 WHEN mod(rn, 2) = 0
4 THEN 'notify'
5 ELSE
6 'temoin'
7 END
8 AS population,
9 *
10FROM
11(
12 SELECT
13 row_number() OVER(ORDER BY count(*) DESC) rn,
14 email,
15 email_md5,
16 n_purchases,
17 status,
18 domain
19 FROM (
20 SELECT
21 email,
22 email_md5,
23 status,
24 CASE
25 WHEN
26 SPLIT_PART(email, '@', 2) IN ('hotmail.fr', 'hotmail.com', 'live.fr', 'outlook.fr', 'gmail.com', 'orange.fr', 'wanadoo.fr', 'yahoo.fr', 'sfr.fr', 'neuf.fr', 'free.fr')
27 THEN
28 SPLIT_PART(email, '@', 2)
29 ELSE
30 'others'
31 END AS domain
32 FROM
33 beauteprivee_database_in bdi
34 JOIN
35 beauteprivee_purchases bp
36 ON
37 email_md5 = LOWER(TO_HEX(MD5(TO_UTF8(LOWER(email)))))
38 WHERE
39 last_purchase_date = ''
40 ) a
41 GROUP BY domain, email_md5
42) b;