Skip to content

Tag: google-bigquery

Big query/sql to maintain the same data from next row onwards in the same column itself, got based on some condition in the current row

I am working on one of the usecase where I need to implement one of the logic to get the relevant data . eg: If you will see the image : The column = new_TARIFF_ALLOWANCE_DATA is calculated as case when gift_given!=0 or gift_received!=0 then TARIFF_ALLOWANCE_DATA – gift_given + gift_received else remain same whatver it is as per last row calculation

Passing tuples to SQL query while unnesting

I need to query a large BigQuery table to retrieve data from certain dates about barcodes in a store. There is a specified date for each of these barcodes (there are thousands of dates for each barcode in the BigQuery table, which makes it unpractical to only query on barcode). I have therefore created a list of tuples containing the

Google BigQuery: join on an join Alias

i try to perform an join on a table that is the result of an other join. But i recieve the error: Table “join_1” must be qualified with a dataset (e.g. dataset.table). I bolded the Problem. Thanks a lot in advance. How can I qualify table join_1? I edited the table, I hope that helps to understand the problem more.

BigQuery count each item in array across table

I cannot quite find what I’m looking for, so here goes: I’m looking for a way to get a count of the number of times an item occurs in an array across the entire table. Imagine you have a table child_names with two columns – user_id and children I know it’s unusual to have two children with same name, but

BigQuery Temp Table Column has no Name

I’m trying to create a temp table in BigQuery, something like: This results in error “CREATE TABLE columns must be named, but column 2 has no name”. I understand that it can’t extract a column name from MAX(t.event_date). Is there a way I can specify a column name? Answer Is there a way I can specify a column name? Use

How to get only first row grouped by multiple columns?

I want to select a few columns, but only keep the first row grouped by the columns. If I use It can give me two rows for one item_no, if item_type and item_name don’t match in the two rows. sample output: How do I make sure I only get the first row grouped by three columns? Expected output: I’m using

A better way to get the last character

I have been working in an exercise, I have 2 values (one with 9 characters and the other with 8) and I have to show 3 columns. The original number The number without the last character Only the last character The Query works but I would like to know a way to improve how I get the last column, I