Don't know what is failing in this query

I was trying to solve the “Most popular email domain” exercise, and I already completed it, however at first I was trying to do the following query, but it keeps getting me an error, don’t know where is failing, any clues?

WITH domains AS(
  SELECT
    SPLIT_PART(email, '@', 2) AS email_domain,
    COUNT(*)
  FROM users
)

SELECT 
  SPLIT_PART(email_domain, '.', 1) AS main_domain
FROM domains

REPLIES

makaroni4

over 3 years Reply

Hi @ecasanova95

That’s an awesome question, I think it’ll inspire a lesson on reading database error messages.

When you run this query, you should see the error message printed in red:

SQL Editor error message

The error message suggests that we have an error in LINE #3 – we need to aggregate by email_domain, otherwise we can’t use COUNT() function:

column “users.email” must appear in the GROUP BY clause or be used in an aggregate function LINE 3: SPLIT_PART(email, ‘@’, 2)

Here’s the fixed query:

WITH domains AS (
  SELECT
    SPLIT_PART(email, '@', 2) AS email_domain,
    COUNT(*) AS users_count
  FROM users
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT
  *,
  SPLIT_PART(email_domain, '.', 1) AS main_domain
FROM domains

Don't know what is failing in this query

REPLIES

WRITE A REPLY