BigQuery cannot use analytic function in SELECT * REPLACE

Question

For a large database project of mine, I have adopted a structure of building tables with nested structs, and combining these tables together (again using structs) into a single large table. I've found this approach significantly DRYs up my code, and reduces having to do various tasks multiple times.

However, I am struggling with modifying the contents within the structs, in particular when trying to use analytic functions rank and percent_rank. This question builds off of this previous question of mine, and I have added a reproducible example below:

#standardSQL
WITH wide_stats AS (
  SELECT 'joe' name, 'bills' team,
    STRUCT(STRUCT(7 AS fga, 5 AS fgm) AS o, STRUCT(8 AS fga, 3 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(3 AS fga, 4 AS fgm) AS o, STRUCT(9 AS fga, 2 AS fgm) AS d) AS t2 UNION ALL 
  SELECT 'nick' name, 'jets' team,
    STRUCT(STRUCT(12 AS fga, 7 AS fgm) AS o, STRUCT(13 AS fga, 7 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(15 AS fga, 7 AS fgm) AS o, STRUCT(22 AS fga, 7 AS fgm) AS d) AS t2
)
SELECT * REPLACE (
  (SELECT AS STRUCT t1.* REPLACE (
    (SELECT AS STRUCT 
      t1.o.*,
      SAFE_DIVIDE(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) AS fg_pct,
      rank() over(order by wide_stats.t1.o.fgm asc) as fgm_rank
    ) 
    AS o))
  AS t1)
FROM wide_stats

This returns the following error SELECT without FROM clause cannot use analytic functions at [13:6] due to the line that starts with rank() over ...

I enjoy using STRUCTs in BQ, but not being able to update the structs in later queries with analytic functions is a significant limitation currently to what I'm trying to accomplish.

Answer 1

below should do the trick (BigQuery Standard SQL)

#standardSQL
WITH wide_stats AS (
  SELECT 'joe' name, 'bills' team,
    STRUCT(STRUCT(7 AS fga, 5 AS fgm) AS o, STRUCT(8 AS fga, 3 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(3 AS fga, 4 AS fgm) AS o, STRUCT(9 AS fga, 2 AS fgm) AS d) AS t2 UNION ALL 
  SELECT 'nick' name, 'jets' team,
    STRUCT(STRUCT(12 AS fga, 7 AS fgm) AS o, STRUCT(13 AS fga, 7 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(15 AS fga, 7 AS fgm) AS o, STRUCT(22 AS fga, 7 AS fgm) AS d) AS t2
), temp AS (
  SELECT *, RANK() OVER(ORDER BY wide_stats.t1.o.fgm ASC) AS fgm_rank 
  FROM wide_stats
)
SELECT * EXCEPT(fgm_rank) REPLACE (
  (SELECT AS STRUCT t1.* REPLACE (
    (SELECT AS STRUCT 
      t1.o.*,
      SAFE_DIVIDE(t1.o.fgm, t1.o.fga) AS fg_pct,
      fgm_rank
    ) 
    AS o))
  AS t1)    
FROM temp