Tag: google-bigquery

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…

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…

I have an input table which has a list of all queries run in Bigquery. I need to create two columns one with the dataset name and other with table name. The queries example is as follows (some could be nested). I need to separate the dataset name and tablename using the dot in the string Queries dataset table…

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…

I have 2 tables that have the exact same columns but different data. The columns are ‘name’, ‘gender’ and ‘count’. The first table is called names_2014 and the second names_2015. My goal is simply to find the top 5 most popular names amongst both these tables. I know that t…

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 …

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 wa…

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 us…