CYBER WEEK - EXTRA SAVINGS EVENT
TRY A FREE LESSON

Why need to rename again as customers_usa_gt_90 for the total count

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

Your Code:

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(cn.customer_id) customers_usa_gt_90**
FROM employee e 
LEFT JOIN customers_usa_gt_90 cn ON cn.support_rep_id = e.employee_id 
WHERE e.title IS "Sales Support Agent"
GROUP BY 1
ORDER BY 1;

I am trying to understand here, why is there a need to rename the total count of customer ID as customers_usa_gt_90. Isn’t there a view already created for that with that name?

3 Likes

Hi @willx,

The view we created has no relation with the column name alias we use. Normally, we can use any alias for COUNT(cn.customer_id). However, here we need to use customers_usa_gt_90 as alias for answer checking purposes:

Instruction

  • Your result should have the following columns, in order:
  • employee_name - The first_name and last_name of the employee separated by a space, eg Luke Skywalker .
  • customers_usa_gt_90 - The number of customer assigned to that employee that are both from the USA and have have purchased more than $90 worth of tracks.

Best,
Sahil

WITH customers_usa_gt_90 AS  **#No.1** 
    (                
    SELECT * FROM customer_usa
    INTERSECT
    SELECT * FROM customer_gt_90_dollars
    )

SELECT **#4**
e.first_name ||" "|| e.last_name employee_name, 
**COUNT(cn.customer_id) customers_usa_gt_90**
FROM employee e 
LEFT JOIN customers_usa_gt_90 cn ON cn.support_rep_id = e.employee_id **#2** 
WHERE e.title IS "Sales Support Agent" **#3**
GROUP BY 1
ORDER BY 1;

Hi @Sahil
just want to understand the logic here. it would hit
No. 1 (subquery)
No. 2 (the joins)
No. 3 (the condition of WHERE)
No. 4 (the SELECT)

Because now we have the alias customers_usa_gt_90, what happens to customers_usa_gt_90 at No. 1 and No. 2? Sorry if this looks too crude. i can draw it out if i am not clear here

1 Like

Hi @willx,

Having an alias with the same name as the subquery will not affect anything. customers_usa_gt_90 at No. 2 will be replaced with the subquery:

(
     SELECT * FROM customer_usa

     INTERSECT

     SELECT * FROM customer_gt_90_dollars
)

And the customers_usa_gt_90 next to the COUNT(cn.customer_id) will be used as the column name in the result.

Best,
Sahil

so, i see - the column name is just that it is intuitive as it is also referring to customers_usa_gt_90.
if i were to refer to customers_usa_gt_90 - it would be the subquery at no. 1. thanks @Sahil ahil for the explanation.

1 Like