Basic query using "WITH" results in "Unrecognized name" error

Question

I know this is a very basic question, I'm just learning SQL, so sorry if I'm missing something obvious.

When I run the below query I get an error:

Unrecognized name: avg_price at [12:15]

WITH avg_price AS
(
    SELECT 
        AVG(UnitPrice)
    FROM 
        `erics-test-data-project.sales.sales` 
)
SELECT 
    UnitPrice
FROM 
    `erics-test-data-project.sales.sales`
WHERE
    UnitPrice > avg_price

I thought I defined avg_price in line 1 (following the basic WITH syntax from BigQuery documentation. I tried enabling legacy SQL dialect but that gave me another error:

Encountered " "WITH" "WITH "" at line 1, column 1. Was expecting: [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]

Answer 1

Try this :

WITH avg_price AS(
  SELECT 
    AVG(UnitPrice) as avg_UnitPrice
  FROM 
    `erics-test-data-project.sales.sales`
)
SELECT 
  UnitPrice
FROM 
  `erics-test-data-project.sales.sales`, avg_price
WHERE
  UnitPrice > avg_UnitPrice