SELECT an aliased column not yet defined

Hey folks,

There seems to be “gaps” in the curriculum. I’m working through the “warby parker” funnel project in the Data Science career path and I’m at the end going through some of the extra questions in step 6.

I’m watching Rob’s video (the help video provided: https://www.youtube.com/watch?time_continue=14&v=lM2Tr7A9W1E&feature=emb_title - time is at 15:08) and he enters the following code:

WITH q AS (
SELECT ‘1-quiz’ COUNT(DISTINCT user_id)
FROM quiz)

My question is specifically related to the SELECT clause where it appears that he grabs an already (not previously aliased as such) aliased column. At this point in the curriculum, we’re taught that we can alias columns in the below manner:

WITH q AS(
SELECT COUNT(DISTINCT user_id) AS ‘1-quiz’,
FROM quiz)

Are the two clauses essentially the same? Can we eliminate the need for the AS clause by simply putting the desired alias immediately following the SELECT clause? This was not covered anywhere previously in the curriculum.

It’s also in the video that I learned we can clean up our code by aliasing the table names.

Example, instead of:

SELECT home_try_on.user id
FROM home_try_on;

use,

SELECT h.user_id
FROM home_try_on h;

Again, no where in the curriculum does it mention these tricks.

Regardless, help regarding the aliasing as requested in the first section of this question would be much appreciated.

Thanks!

1 Like

I also can’t recall where they mention aliasing tables. But, it is different than aliasing columns in a table. With tables, you don’t always need to use AS.

Think of it as shorthand. It’s easier to read your query, esp. if it’s lengthy and involved. Plus, it’s only a temporary table change (or column name change). It doesn’t actually change the name of the table in the DB.

More on that here:
https://www.tutorialspoint.com/sql/sql-alias-syntax.htm

Thanks for that link.

I’m still a bit lost particularly around the instructors code, below:

“SELECT ‘1-quiz’ AS stage”

What EXACTLY is happening there? The results return “stage” as a column and “1-quiz” as row but I am lost as to how that information is returned.

I also found another forum regarding this same part of the project (link below) that poses a good question that never got answered - it would also bring more clarity.

Thanks!

I did this Capstone project as part of a CC SQL Intensive Jan-Feb 2019. (It’s no longer offered and I think it’s part of the DA path now(?)) Anyway, I’m thinking that the help video was produced well after that (b/c I don’t recall that video on the CC platform at all.) So, I think that’s why there’s some discrepancies in the video content and what’s presented in the lesson.
In the video, ‘1-quiz’ is the first part of the funnel, the 1000 visitors to the site. In the previous query, you’re pulling out the DISTINCT users for each stage of the funnel.