I would like to add a calculation taking into account we could have one, two, three or a lot of more rows. We can get all the data using the relations but I am a bit stuck because the number of relation are undefined. For example, as a source: Source Table id amount debt relation rn 123 250 225 NULL
Tag: google-bigquery
How to get an array of user_id’s being active at a specific point in time based on their latest event in BigQuery?
I have a problem where I’d like to get all user_id’s in the “active” state for each day. An event is recorded only when the state changes for a user. The state for each user_id should be preserved until an “inactive” event is fired (see example data and outcome). Different …
Is there an elegant way to aggregate data by groups based on elements of an array in SQL?
I’m looking for a way to aggregate data by groups that are based on elements in an array. I’m working with product and sales data in a BigQuery warehouse, and want to measure the sales of collections of products, where each product may be in more than one collection. This is probably best explaine…
How can I extract all text between single full stops (i.e. ignore `…`) with a re2 regex?
How can I extract all text between single full stops (i.e. ignore …) with a re2 regex? I am using the REGEXP_EXTRACT_ALL function in BigQuery which uses https://github.com/google/re2/wiki/Syntax. From the following example: This is… a.. sentence. It is just an example. I would like the query to ex…
Statistical functions on columns of arrays in BigQuery
If I have data that looks like the follows: +——+————+——-+ | a | val | val2 | +——+————+——-+ | 3.14 | [1, 2, 3] | [2, 3]| | 1.59 | [7, 8, 9] | … | | -1 …
Calculate Time Between Logins by User in Bigquery
I am trying to calculate the time difference between 2 login events in BigQuery, by user. I want to understand how long it takes between logins for each user. I’ve tried with the following query: Sample Data user_id event timestamp aaa Login 2021-02-20 00:00:00 UTC bbb Login 2021-02-20 00:02:00 UTC aaa …
WHERE clause expression references [casted timestamp to date column] which is neither grouped nor aggregated at
This query should sum up all the records by Error_Type (nested) and by Application(nested) in the Failed_Record_Count column. Total_Record_Count column should sum up all the records from the table(excluding unnested tables). However, an error referencing to a.timestamp in the subquery is said to be neither gr…
Calculating timestamp difference in seconds in BQ
I am trying to calculate the timestamp difference between install_time and attributed_time, in seconds. However, I keep receiving this error: No matching signature for function TIMESTAMP_DIFF for argument types: TIMESTAMP, STRING, DATE_TIME_PART. Supported signature: TIMESTAMP_DIFF(TIMESTAMP, TIMESTAMP, DATE_…
Average on most recent date for which data is available for multiple columns
I have a table (on BigQuery) that looks like the following: What I would like to get is the average score for each type but the average should be taken only on the most recent date for which at least one score is available for the type. From the example above, the aim is to obtain the following table in
Unable to unnest in bigquery when there is comma inside list which is of type string
Unable to unnest in bigquery when there is comma inside list which is of type string. I have data in following format where value is type of string When I use following query, It is incorrectly splitting and unable to get logic right in regexp_replace With the above code, I am getting My expected output is I …