Tag: google-bigquery

I am taking a database of statuses and creating the statuses as columns in order to count how many records from a network exist in each status. I’d love to sort the results based on the Partnered column DESC, but I can’t figure out how or where to do that?? Here’s my code: Here are my result…

Iam using Bigquery to insert nested records into the table but eventually landed into error scenario. Schema: Code: with the above code, I couldn’t able to load the values into table. Please help me by letting know how exactly we can pass values into nested records. Answer Try below instead

I’m trying to get the primary email from aliases, for example, I have the following examples and I need to normalize them. from “test.me@email.com” to “testme@email.com” from “test+me11@email.com” to “test@email.com” from “test.me.123+22@email.com&#8…

I am trying to extract some information from all tables in the SALES dataset. I want to run it for multiple clients. Each client has a big query project. Bellow query returns what I want for our London client. How I can use a wildcard to do the same query for the other 10 clients and union all in one

I think this is a more complex version of the question in Update values in struct arrays in BigQuery. I’m trying to update some of the fields in a struct, where the struct is heavily nested. I’m having trouble creating the SQL to do it. Here’s my table schema: This is what the schema looks l…

I’m trying to create a Stack Overflow question where, in order to ask the question, I need to produce a minimal example. As I create the minimal example, I use BigQuery DDL to create a table. The table create step succeeds, inferring that the schema I chose is fine. But the INSERT INTO statement I wrote…

I want to sum over 3 rows (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) using SQL ROLLING WINDOW: The issue is that if try to use the statement ROWS BETWEEN 2 AND CURRENT ROW, it will provide SUM even if I have only ONE preceding row, and I would want to have result as NULL / 0 if there’s not

Let’s say I have col1 col2 a c b d and I want to have only one column, I’d do and get col1 a b c d But now let’s say I have 100 columns (all strings, same data type), what’s the best way to add all the columns together without doing a union all for all the columns? I