I’m trying to find in some chatbot logs the users that reached the last intent, but I need the intent before that one. i.e. Table 1 ID Intent 1 last 1 first 2 first 3 second 4 last 4 second 4 first would need to get this ID Intent 1 first 4 second Answer Consider below approach (note you need
Tag: google-bigquery
Is there a way to add “Virtual” Row in BigQuery?
What I mean by “virtual” is the following: For example, you have the following table, And when you run the following query: it returns the new table by adding “virtual” column My question is, is there a way to add a new “virtual” row, I mean without actually modifying the table or using the INSERT statement. I think the code
SQL script within subquery (Big Query)?
Is there a way to have SQL scripts within a subquery ? For example: In my case it is not possible to move the variable declaration outside of the subquery. Thanks ! Answer For this particular example, you could use a CTE to define the parameter values:
How to compare DD/MM dates in BigQuery?
I’m trying to compare two dates without the year, only the day and the month (DD/MM) when for example 30/04 < 02/05. I want to be able to say wether a date is previous or not to another date based only on the day and the month without paying attention to the year. I tried to use FORMAT_DATE(“%d/%m”, DATE(“2021-04-30”)) <=
If null value in a Column A, then how to return the value of column B, and if also B is null, then how to move to the value of C
Have a table in Google Big Query with date, id and store columns where the goal is to keep track how many stores a customer has visited: I wish to create a SQL query which checks for date in first column (A_date). If it has a date (is not null) then keep the date If it is null then check
Aggregating data in BigQuery by month
I feel silly asking this because it seems simple, but I concede and need some help. In BigQuery I have a Table that looks like this: Date Portfolio Super_Discipline Dollars Units 2020-05-20 …
Using LAG() on an UPDATE with CASE statements
I have a query on a Python Dashboard (using Plotly/Dash) that let users set some variables and update a table in BigQuery. I’m having trouble with one query that has some CASE statements and using LAG(). I wrote the following query: But I got the following error: “Analytic function not allowed in UPDATE clause…”. I researched about it and it’s
How to pivot in BigQuery?
I’m doing validations between source tables and destination tables in different processes in a Data Lake. I work with StandardSQL in BigQuery, and I do the comparisons between both tables with this query: And this is the result: Is there a way to normalize and denormalize this information as follows? I have to evaluate several processes that have more or
How to turn a number to zero if the result of a calculation is negative inside a squareroot in BigQuery?
I have a query that can result in a negative squareroot, leading to an error. I’m using BigQuery. Is there a way to turn the result in to a 0 if inside de POWER() function is less than 0? Here’s my query: The negative squareroot can happen here: So, if the result of is less than zero, I would like
Calculate working hours between exit date and 3 months before exit date bigquery sql
I’m trying to calculate the total working hours between two dates in bigquery sql: The dates being between MAX(date) and DATE_SUB(MAX(date), interval 3 month). In other words, I want to know the sum of working hours between the exit date and 3 months prior to the exit date. The current table is something like this: id date hours abc 2020-10-01