Attempting to use CTE in Big Query

Question

I know this isn't the best way to achieve this, but I'm just playing around with things as I continue to learn. I used a CTE to join two tables and now I'd like to aggregate off of the temporary table.

I keep receiving the following error in various ways, seems like BQ is telling me that it doesn't recognize the values in the second statement:

Name accounts not found inside accounts_Opps at [15:22]

Here's the query:

WITH accounts_Opps AS (
  SELECT  Accounts.Id, Accounts.name,  
          Accounts.sales_territory_c, 
          Accounts.cs_stage_c,
          opportunities.account_Id,
          opportunities.name,
          opportunities.rep_projected_arr_c,
          opportunities.stage_name
  FROM `salesforce.account` AS Accounts
  LEFT JOIN `salesforce.opportunity` AS Opportunities
  ON Accounts.id = Opportunities.account_id
  WHERE Opportunities.account_Id IS NOT NULL
)

SELECT accounts_Opps.accounts.name, count(accounts_opps.opportunities.rep_projected_arr_c)
FROM accounts_Opps
GROUP BY accounts_Opps.accounts.name

Again, although I know this isn't the best way to achieve this, I'm just playing with different approaches and I'm wondering if this approach would work and if so, how?

Answer 1

You are dealing with duplicate names, one from Accounts and another from Opportunities, which is not feasible. Columns that have identical names can be differentiated by assigning aliases.

As pointed out in the comments by @shawnt00, the columns that come out of the CTE are recognized by their original name or by the alias assigned in the select statement.

WITH accounts_Opps AS (
  SELECT  Accounts.Id, 
          Accounts.name as account_name,  
          Accounts.sales_territory_c, 
          Accounts.cs_stage_c,
          opportunities.name as opportunity_name,
          opportunities.rep_projected_arr_c,
          opportunities.stage_name
  FROM `account` AS Accounts
  LEFT JOIN `opportunity` AS opportunities
  ON Accounts.id = opportunities.account_id
  WHERE opportunities.account_Id IS NOT NULL
)

SELECT account_name, count(rep_projected_arr_c)
FROM accounts_Opps
GROUP BY account_name