· 8 years ago · Nov 25, 2017, 04:04 AM
1# Top 5 email providers members use
2
3select
4 count(*) as users,
5 regexp_extract(email, '@(.*)', 1)
6
7from profile
8
9group by
10 regexp_extract(email, '@(.*)', 1)
11
12sort by
13 users desc
14
15limit 5;
16
17users email
1812044348 yahoo.com
196603732 hotmail.com
201934764 aol.com
211545762 gmail.com
22535849 hotmail.co.uk
23
24# Top 15 email providers our US members use by state
25
26select
27 count(*) as users,
28 region,
29 regexp_extract(email, '@(.*)', 1)
30
31from profile
32
33where
34 country=231
35
36group by
37 regexp_extract(email, '@(.*)', 1),
38 region
39
40sort
41 by users desc
42
43limit 15;
44
45users region email
46926646 12 yahoo.com
47721138 67 yahoo.com
48591192 52 yahoo.com
49530616 17 yahoo.com
50376314 6 yahoo.com
51340118 53 yahoo.com
52338708 24 yahoo.com
53318686 19 yahoo.com
54302362 12 hotmail.com
55291388 41 yahoo.com
56281531 57 yahoo.com
57277348 34 yahoo.com
58220485 69 yahoo.com
59195235 25 yahoo.com
60190345 67 hotmail.com