How to answer this hard sql?

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, "@"),".")"."));


Source: sql

Leave a Reply