Suppose I have the following table t_1 where every row represents a day: How can I create the following table t2 from t1? Here val_cur is defined as the sum of values of the current timeframe (i.e. the sum of values between date_start and date_end) and val_prev is defined as the sum of values of the previous timeframe (i.e. the
Tag: google-bigquery
A string does not contain a number
Looking for addresses in a table that does not contain a number So “Smith Street” or “James Road” etc I tried using: address_street not like ‘%[0-9]%’ but this did not work, as the results returned everything that is not literally that string. Answer For this kind of investigation with strings you can use regular expressions and the corresponding functions offered
SQL BigQuery – how can I calculate for each order the the count of that customers’ orders in the preceding full 12 month period
For each order record I would like to calculate the count of prior orders, from that customer, over the previous full 12 month period (excluding the month of the order). I can get the count work without the date limits (code below). But I just can’t figure out how to limit the count to the ‘rolling’ date range. I’d really
Fuzzy matching multiple columns in BigQuery when left-joining
Example We are looking to join rhs_table onto lhs_table for the playerIds. Every person in lhs_table has a corresponding row in rhs_table, however the joins are not so simple: For Nia Johnson Jr., Jr. is missing in rhs_table For Kay Sieper, her full name Kayla is used in rhs_table We want to ignore (ie not left join) RHS players on
How to add up a string of numbers using SQL (BigQuery)?
I have a string of numbers like this: I want to add up these numbers which in the above example would result in 14: 6+7+0+…+1+0+…+0+0+0=14 How would I do this in BigQuery? Answer Consider below approach with output
How to find the index of an element in an Array in BigQuery
is it possible to find the index of an element in a array in BigQuery? The idea is to find the index of an element to grab a different element in the row below. So for example, for this case I need to find the index of ‘status2’ to grab the respective entering date of the status after status2, that
How to remove duplicated rows from table with arrays in BigQuery
there is a table in BigQuery that has REPEATED type columns and has duplicated rows, since the table has arrays I cannot use distinct to grab only one row. Table looks something like this: I want to remove the duplicated rows, the output should be like this: I didn’t find a way to come up with the above result, anyone
How to change arrays into rows
There is a table in BigQuery that contains 2 REPEATED(arrays) type of columns, enterded_date and status. Table in BigQuery: Is it possible to make a query that returns rows instead? Like this: Answer Consider below approach if applied to sample data in your question – output is
How do I add number of days from original dates in new column
Hopefully a quick one on BigQuery I’ve tried intervals and days but can’t quite seem to get what I want. For date row on the example table below I want and adjacent row in a new column that just adds 42 days to the original date and time (time is always 00:00:00 if that helps). Desired output below: original_datetime date_time_plus_42_days
Return only ALL CAPS strings in BigQuery
Pretty simple question, specific to BigQuery. I’m sure there’s a command I’m missing. I’m used to using “collate” in another query which doesn’t work here. Desired return: JOHN@EMAIL.COM,STACY@EMAIL.COM Answer Consider below If applied to sample data in your question – output is In case you want the output as a comma separated list – use below with output You can