Analytics Engineering, Bigquery, Sql, Data Engineering, Editors Pick.
that looks simple at first sight but is rather tricky to do with SQL. This is the kind of advanced transformation where I recommend considering the trade-off between writing the logic in pure SQL vs. writing a User Defined Function (UDF) in another language (Python, Scala, Javascript, or whatever),