Could someone help me understand why these two queries are returning different results in bigquery? select FORMAT_TIMESTAMP(‘%F %H:%M:%E*S’, “2018-10-01 00:00:00” , ‘Europe/London’) returns …
Tag: google-bigquery
DATE_DIFF() in BigQuery to calculate time between rows
I would like to calculate the time delay between several customer purchases. However, each purchase is saved in an individual row. The data set looks similar to the following: customer | order_id | …
Calculate order sequence in BigQuery
I am new to BigQuery SQL and struggling with calculating an “order sequence” from a table of customer orders. In my example, I am working with customer purchases, where I have the sales …
Bigquery SQL MAX() text value
Thanks for taking the time to look at this. How to get the MAX of a text value in column A. I would like to and another where clause to show only one of the “FY20-Q4M#” (MAX value only) Current Table YY-QQ_STATUS Program FY20-Q2_ACTUALS XYZ FY20-Q3_ACTUALS XYZ FY20-Q3_BUDGET XYZ FY20-Q4M0 XYZ FY20…
Need a SQL Server query to eliminate the Highlighted Rows ( Returning Routes in Flight)
I have a requirement where I need to eliminate all the rows which have returning flight routes. I have highlighted the routes in the orange color which depicts this behavior. For example, row 1 has one route BKI – MYY and row 4 has MYY – BKI. I need a flag (Boolean 1/0) that checks the entire row …
“Flattening” complex sequences of related rows in SQL
I have a table with events describing user navigation through flows, with this general shape (disregard the timestamps, I smashed some keys): After the option_a event, users can go back and choose another option: Users can also go through a flow multiple times: I want to produce a table where each row represe…
Find Previous Month MTD (Month to Date) Calculation in BigQuery
Assuming I have initial table of date and each order value of the date: So I use this query for calculating current MTD (cumulative gross order in a month from day 1 of certain month to certain date in that month), the result will be something like this: The idea is to also have value of previous month MTD va…
Condition OFFSET on SPLIT in SQL
I am using SQL on Google Big Query. I have a column MyColumn which value is in this format : Text0;Text1;Text2;Text3;Text4;T12=12/T26=5/T13=1/T55=ABC I need to fetch the value of T13 (“1” …
How to give names to output tables?
I need to do a JOIN using two ouput-tables (these tables result from previous operations using other tables). I want to give them specific names to reference them. I’ve try ALIAS, AS, INTO, but nothing works. I am new in SQL and I’d really like some help here. My code is the following I’d li…
SQL (Bigquery) – Populate multiple columns in existing rows
EDIT: As @Prateek suggested, I changed my working flow – Now the first query is used as “Structure-Query” that I run every time that I want to clear and recreate my table with the desired variables structure. The second query became my “Population-Query”, where I fill my empty st…