Use row_number() in BigQuery in CTE

Question

I'm trying to include row numbers per orderId with this query but I get a error message saying "Unrecognized name: BQ" when selecting the columns in the subquery. I havn't used it too much so not sure where I'm doing it wrong. Can anyone see it?

     WITH BQ AS(
        SELECT 
        (SELECT customDimensions.value FROM UNNEST(t.customDimensions)
        AS customDimensions WHERE customDimensions.index = 6) as
        orderId_bq, 
        hits.eventinfo.eventaction as event_action, 
        hits.transaction.transactionId as trx_id, 
        hits.page.pagePath as page, 
        hitnumber AS hitnumber 
    
    FROM `xxxxx-xxxx.xxxxxx.ga_sessions_20210801` t,
    
    UNNEST(HITS) as hits 
    WHERE (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 8) = 'se'
    AND (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 4) = 'soffadirekt'
    --AND hits.eventinfo.eventaction IN ('complete purchase')
    --AND hits.transaction.transactionId IS NULL
    --and hits.page.pagePath != '/backend-transaction'
    --and hits.eventinfo.eventaction != 'backend transaction' )
    
    )
       
    SELECT 
    BQ.event_action,
    BQ.trx_id,
    BQ.page,
    BQ.hitnumber,
    
    FROM (SELECT Row_number()
            OVER( PARTITION BY BQ.orderId_bq
            ORDER BY BQ.hitnumber) as RN,
            BQ.orderId_bq
    
    from BQ
)

I did also try this but then it doesn't regognize 'flat.orderId' instead:

WITH BQ AS

(SELECT 
(SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 6) as orderId_bq,
hits.eventinfo.eventaction as event_action,
hits.transaction.transactionId as trx_id,
hits.page.pagePath as page,
hitnumber AS hitnumber 

FROM `xxxx-xxxxxxxx.ga_sessions_20210801` t,
UNNEST(HITS) as hits 
WHERE (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 8) = 'se'
AND (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 4) = 'soffadirekt'

),

flat AS (
SELECT 
*
from bq
)

SELECT 
flat.orderId_bq,
flat.event_action,
flat.trx_id,
flat.page,
flat.hitnumber, 
FROM (SELECT Row_number()
        OVER( PARTITION BY flat.orderId_bq
        ORDER BY flat.hitnumber) as RN,

flat.orderId_bq,
flat.event_action,
flat.trx_id,
flat.page,
flat.hitnumber 

FROM flat
)

Answer 1

Error Unrecognized name: BQ happens because your are trying to query BQ.* that does not exist since you did not add the BQ alias on your subquery. Adding AS BQ should work. See query:

SELECT 
BQ.event_action,
BQ.trx_id,
BQ.page,
BQ.hitnumber,

FROM (SELECT Row_number()
        OVER( PARTITION BY BQ.orderId_bq
        ORDER BY BQ.hitnumber) as RN,
        BQ.orderId_bq
        FROM BQ) AS BQ

Just a suggestion, it might be better to use a different alias so your query will be much readable.

I tested this using a table of mine where I add AS subq1 at my subquery. See a simple test:

WITH subq1 AS (SELECT amount_paid,customer from `my-project.test_dataset.myTable`)

SELECT
  subq1.RN,
  subq1.customer,
  subq1.amount_paid
FROM
  (SELECT ROW_NUMBER() 
    OVER(ORDER BY subq1.amount_paid) as RN,
    subq1.customer,
    subq1.amount_paid 
    FROM subq1) AS subq1
  LIMIT 3

Results: