The IT Manager of Chinook is interested to find out which email providers their customers are
associated with. Write a single SQL statement to generate a list with 2 columns named Email
Provider and No. of Customers. The email provider should be displayed in all upper cases (e.g.
GMAIL, YAHOO) and such information could be obtained from the email address of the customers.
Email addresses from the same provider with different country codes (e.g. yahoo.com, yahoo.de,
yahoo.ca) should be treated as one email provider. Sort the result of the query first by No. of
Customers in decending order, and then by Email Provider in assending order.
What I have tried:
SELECT Substr(email, Instr(Email), "@")+1, 25-Instra(Substr(Substr(Email, "@")," "),".") AS "Email Provider", COUNT(*) AS "No of Customers" FROM Customer GROUP BY UPPER(substr(Email,Instr(Email, "@")+2,Length(Substr(Substr(Email, "@"),".")"."));