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
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
I’ve got a table with lat and lng coordnates, and need to add the distance into a new column called ‘distance’ in Bigquery. table start_lat end_lat start_lng end_lng 41.8964 41.9322 -87.661 -87.6586 41.9244 41.9306 -87.7154 -87.7238 41.903 41.8992 -87.6975 -87.6722 I haven’t a clue how to do it. I saw some examples, but simply couldn’t apply it into this case.
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 select
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.
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 to get the most popular names for one table is: However,
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
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
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
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