Help me debug my query - or is the bug not mine?

Q&A DQ Courses
#1

Screen Link:
https://app.dataquest.io/c/46/m/190/building-and-organizing-complex-queries/6/combining-rows-using-intersect-and-except

My Code: 

WITH custs AS
(
    SELECT *
    FROM customer_gt_90_dollars
    
    INTERSECT
    
    SELECT *
    FROM customer_usa 
)

SELECT
    e.first_name || " " || e.last_name AS employee_name,
    COUNT(c.customer_id) AS customers_usa_gt_90
FROM employee AS e
LEFT JOIN custs AS c ON c.support_rep_id = e.employee_id
WHERE e.title = 'Sales Support Agent'
GROUP BY e.employee_name
ORDER BY e.employee_name;

What I expected to happen:
I thought this would be correct, failing that I expected it to run.

What actually happened:
I get the error below

(sqlite3.OperationalError) no such column: e.employee_name
[SQL: WITH custs AS (     SELECT *     FROM customer_gt_90_dollars          INTERSECT          SELECT *     FROM customer_usa  )  SELECT     e.first_name || " " || e.last_name AS employee_name,     COUNT(c.customer_id) AS customers_usa_gt_90 FROM employee AS e LEFT JOIN custs AS c ON c.support_rep_id = e.employee_id WHERE e.title = 'Sales Support Agent' GROUP BY e.employee_name ORDER BY e.employee_name;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

If I refresh the screen and copy and paste the answer it runs correctly.
If I comment out my code and paste the correct answer it seems to run, but there is no output.
If I comment out my code and paste the correct answer and submit the answer I get the following error:
Code

-- WITH custs AS
-- (
--     SELECT *
--     FROM customer_gt_90_dollars
    
--     INTERSECT
    
--     SELECT *
--     FROM customer_usa 
-- )

-- SELECT
--     e.first_name || " " || e.last_name AS employee_name,
--     COUNT(c.customer_id) AS customers_usa_gt_90
-- FROM employee AS e
-- LEFT JOIN custs AS c ON c.support_rep_id = e.employee_id
-- WHERE e.title = 'Sales Support Agent'
-- GROUP BY e.employee_name
-- ORDER BY e.employee_name;

WITH customers_usa_gt_90 AS
    (
     SELECT * FROM customer_usa

     INTERSECT

     SELECT * FROM customer_gt_90_dollars
    )

SELECT
    e.first_name || " " || e.last_name employee_name,
    COUNT(c.customer_id) customers_usa_gt_90
FROM employee e
LEFT JOIN customers_usa_gt_90 c ON c.support_rep_id = e.employee_id
WHERE e.title = 'Sales Support Agent'
GROUP BY 1 ORDER BY 1;

Result

The value for result doesn't look right.
One of your variables doesn't seem to have the correct value. Please re-check the instructions and your code.

This seems like a bug, but I thought it would be good to put other eyes on this before I report it.

#2

Hey, Emily.

I agree, this seems like a bug.

Now, about your code, notice the following lines:

GROUP BY e.employee_name
ORDER BY e.employee_name;

Now take a look at the employee table again. Pay careful attention to its columns. Do you see employee_name there?

I hope this helps.

#3

Bruno - thank you for the help! Since in the SELECT clause the alias for the concatenated first_name and last_name field is employee_name shouldn’t that be available in the GROUP BY and ORDER BY clause? Otherwise, how can I order by the concatenated field?

#4

You can do this by ending the query with ORDER BY employee_name. Notice that I removed e. relative to your answer.

Unfortunately there isn’t a clear short answer. The shortest answer is “Yes and no and it depends”.

However, despite this, you’re doing something more specific than making it available to those clauses. You’re saying employee_name comes from the employee table (e.employee_table), hence my hint in my previous reply.

That’s why, regardless of the answer to the question, this part wouldn’t be correct.

Now for the long answer to the question. You can remove e. and it will work (in the sense that you will get the correct result and pass the screen). However, it’s inadvisable to GROUP BY aliases like this. The reason is that it won’t work in many other popular database management systems.

SQL follows the following order in executing the main query:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. SELECT
  5. ORDER BY

So, in principle, GROUP BY doesn’t know the aliases as they’re defined in SELECT (which only runs after GROUP BY). It works in SQLite (the SQL we’re using in this course) because it is a special feature of SQLite.