I’m beginner on StandardSQL My Table : I need your help to get the transactionId to be duplicated on each row instead of the Blank. My Query : Answer You already have transactionId on each row. Your query generates rows where each row has transactionId and some array. The BigQuery GUI just formats array…
Tag: google-bigquery
BigQuery SQL to track dwell time in point of interest from location data with multiple stops
I’m working on a dataset with anonymized location data and I receive a series of time-stamp intervals at various points of interest. I’m trying to calculate the dwell time for an individual within a point of interest. I’ve tried a simple method of max(timestamp) – min(timestamp) to cal…
Deduplicate table SQL with nested rows (type STRUCT)
I have a SQL table (in BigQuery) with possible duplicated rows. The table has over 20 columns, some of them are nested (data type “STRUCT)”. I want to deduplicate the table. I can’t simply query SELECT DISTINCT * because I get an error Query error: Column options of type STRUCT cannot be use…
How to count monthly retention user in bigquery?
I have raw data as below. With each line is the record of an transaction of user, and the month when they made the transaction What I want is to calculate the number of user who made order in a month and the number of repeated user (RETENTION) from last month, then I can know how many % of user
BigQuery Standard SQL SELECT rows WHERE field contains words from another table field
I have 2 tables in BigQuery. VIDEOS table contains video names and tags. CREATORS table contains information about video creators. The VIDEOS.tags field contains comma separated quoted strings. I need to select all the videos that were tagged with names in the CREATORS table with results looks like this: But …
Modify data in a specific format
I want to represent data in a specific format. Currently, the data looks like below- I want to represent it in the below format- Is is there a way to get the data in this format? Answer You want to unpivot and filter. Here is a BigQuery’ish way to do this:
Regular Expression to extract string after seeing “number + one letter + [comma or whitespace]” in Bigquery
I am trying to extract: Abbey Grove Abbey Grove Abbey Road View Abbey Road Abbey Terrace Abbey Wood Road Abbey Grove from 23a, Abbey Grove 43a Abbey Grove Block 509a Abbey Road View 511 Abbey Road …
How to get “session duration” group by “operating system” in Firebase Bigquery SQL?
I try to get the “average session duration” group by “operating system” (device.operating_system) and “date” (event_date). In the firebase blog, they give us this query to get the average duration session This query give me the total user engagement by user ID (each row is …
In BigQuery, match two tables according to a calculated value of one table
I have two tables in BigQuery: one containing the places of road cameras, named cameras… …and another containing accidents, named accidents. I need to inspect how many accidents happened in the range of 0.5 mile before and 0.5 mile after the cameras. So, what I need to do is: Establish a .5-mile p…
SQL – get specific text from string
I have column with this strings: I need to get from the string ONLY the contact_type__* part (appears only once in each row) Any suggestions? Answer You could use regexp function regexp_extract(), like: This will match on string ‘contact_type__ followed by a sequence of letters, numbers, or underscore (…