I have the next row and what I want to do is to select all the rows before the type “shop”. I tried using case in the “where clause” but I didn’t get any result. How can I do it? |id|visitnumber|type …
Tag: google-bigquery
GBQ SQL: Using IF statement in a WHERE statement to filter data
Looking to filter data when multiple conditions are met. The table I have is below: I need to keep rows that fulfill either one of the follow criteria: If STOCK > 0 THEN KEEP ROW If STOCK <= 0 AND TIME is within 2 weeks from today THEN KEEP ROW The result table should look like this: For my WHERE
BigQuery: need a clever solution for a difficult query
The context of this problem is a Live Chat service. Each conversation consists of multiple messages of which the timestamp is registred. Conversations are held in one or more channels, depending on the difficulty of the inquiry. The picture below shows an example of the data. For testing purposes the sheet can be found here. In the picture you see
Aggregrate the variable from timestamp on bigQuery
I am planning to calculate the most frequency part_of_day for each of the user. In this case, firstly, I encoded timestamp with part_of_day, then aggregrate with the most frequency part_of_day. I use the ARRAY_AGG to calculate the mode (). However, I’m not sure how to deal with timestamp with the ARRAY_AGG, because there is error, so my code structure might
GCP Bigquery – query empty values from a record type value
I’m trying to query all resources that has empty records on a specific column but I’m unable to make it work. Here’s the query that I’m using: SELECT service.description, project.labels, cost …
SQL return max in unnest
I have the following query: What should I do if I only want the query to return one array.col1 per row in table_a (let’s say I want only the max value for each case)? Answer You can use a subquery:
Group rows based on column values in SQL / BigQuery
Is it possible to “group” rows within BigQuery/SQL depending on column values? Let’s say I want to assign a string/id for all rows between stream_start_init and stream_start and then do the same for the rows between stream_resume and the last stream_ad. The amount of stream_ad event can differ hence I can’t use a RANK() or ROW() to group them be
Calculating average time (in days) between customer first and second order on BigQuery
I’m trying to figure out the avg days it takes for a customer to repeat purchase. Here is what I have It’s returning this I want to now get the average days between all first and second purchases, then second and third et Sample data set |———————|——————|——————|——————| | Email_address | Order_name | Order_number | Date | |———————|——————|——————|——————| | test@test.com |
Left join matching between dates
I am trying to pair the date of a national holiday with time bucket boundaries. I would like to left join against buckets. So I can identify if a holiday falls within a certain time frame has_holiday. With the following query, I receive a Query error: Query error: LEFT OUTER JOIN cannot be used without a condition that is an
BigQuery: lowest timestamp inside an array of objects and average between specific timestamps
Scenario: user has a subscription where the size of the product can be updated. This results in the following data set Here’s a more visual example The subscription has a current size and an optional size history, which contains the updates and when they have been made. My goals are to: get the initial size of each subscription. If there