[BigQuery Advanced SQL] find greatest/largest/max non-null values among multiple columns

SQL non-null greatest for multiple columns

-- Below is a fancy version of non-null-greatest() for multi-columns. 
-- it is more extensible for more two columns.
WITH base AS (
  SELECT
    (SELECT ARRAY_AGG (x IGNORE NULLS) AS Y FROM UNNEST ([col_1, col_2, col_3, col_4]) AS x)
    AS array,
  FROM source_table AS nl
)
SELECT
  (SELECT MAX(y) FROM UNNEST(array) AS Y
  ) AS non_null_max_value
FROM base


-- below is a simpler version for two columns 
SELECT 
  COALESCE(
    GREATEST(col_1,col2),
    col1,
    col2
  )
FROM source_table