Unrecognized name: error in nested query using bigquery standard sql

I am running the query where the inner statement (in this case I use when..case) and it is expected to run the query from the outer statement’s result. But, the result from the outer statement is in string datatype , so I added “as INT64” to solve this. However, the query still throw error “Unrecognized name: (score at [12:33]“

Code:

SELECT
   User_ID, 
   CASE 
     WHEN ..
     ELSE ..
    END AS score,

    (SELECT ROUND(AVG(SAFE_CAST(score as INT64)),1) 
          FROM cognitivebot2.chitchaxETL.conversations )
                AS Average_score
 FROM table

Is there any way to do like keep the query result from outer statement , and then run it in inner statement?

Answer

Do the original query in a derived table, and then use its result column score in the outer query.

You probably need to add a condition too, to make the subquery correlated.

select t.*,
    (SELECT ROUND(AVG(SAFE_CAST(score as INT64)),1) 
          FROM cognitivebot2.chitchaxETL.conversations
          WHERE t.xx = yy                                 <-- Add correlated condition
    ) AS Average_score
from
(
SELECT
   User_ID, 
   CASE 
     WHEN ..
     ELSE ..
    END AS score
FROM table
) t