Where are your users from? – (By SQL Email Domain)
April 19th, 2009
Ever wondered who your users use as their email provider? Who are the biggest email providers of the users on your website? Luckily SQL has the answer in 1 easy step using the SUBSTRING_INDEX string function.
mysql> SELECT COUNT(*) AS Total, SUBSTRING_INDEX(email, '@', -1) AS Domain FROM users GROUP BY SUBSTRING_INDEX(email, '@', -1) ORDER BY COUNT(*) DESC LIMIT 10;
+-------+------------------+
| Total | Domain |
+-------+------------------+
| 1000 | hotmail.com |
| 999 | yahoo.co.uk |
| 888 | aol.com |
| 777 | btinternet.com |
| 666 | yahoo.com |
| 555 | hotmail.co.uk |
| 444 | gmail.com |
| 333 | ntlworld.com |
| 222 | tiscali.co.uk |
| 111 | eircom.net |
+-------+------------------+
10 rows in set (0.00 sec)
mysql>
Categories: SQL
Most People subscribe via RSS Feed
Add to del.icio.us
Stumble It
[...] as essas queries, vi uma interessante consulta para agrupar o número de usuários por domínio de email, através da função substring. [...]