Advertisements Try to understand the BigQuery behaviour of the ANY operator (see also here The following statement returns as expected true: SELECT ‘a’ LIKE ANY (‘b’,’%b%’, ‘b’, ‘a’); –true It basically answers the question, is there any ‘a’ in the pattern, yes it is! Now inverting the statement also returns true: SELECT ‘a’ NOT LIKE… Read More BigQuery `LIKE ANY` and `NOT LIKE ANY`

Advertisements I am trying to form a query that will count distinct rows from multiple tables based on a variable input by the user. However, one or more tables may return zero results for the {{Customer}} variable passed, When I run the query passing a {{Customer}} that has data in all tables, I get the… Read More BigQuery – All results zero when subquery returns no results

Advertisements I’m trying to find the count of unique customers who received 1/2/3 emails from the table below: cust_id month email_cnt 1 202311 1 1 202311 1 1 202310 1 1 202310 0 1 202309 1 2 202311 0 2 202311 0 2 202310 0 2 202310 1 2 202309 1 3 202311 0 3… Read More Unique customers who received 1, 2, or emails

Advertisements I have the below table that shows a monthly view of customers that received an email in a particular month. cust_id eom_date email_sent 1 2023-06-30 1 1 2023-05-31 1 2023-04-30 1 1 2023-03-31 1 2023-02-28 1 2023-01-31 1 2 2023-06-30 1 2 2023-05-31 2 2023-04-30 1 2 2023-03-31 2 2023-02-28 2 2023-01-31 2 2022-12-31… Read More Number of months since last email received

Advertisements I have a column with suburb names and postcodes – eg – MARLEY HEIGHTS 2067. I want to remove the last 5 characters and get the suburb name only. i.e., MARLEY HEIGHTS as the result. How can I do that in big query ? I tried Rtrim(column,’ ‘) as suburb, but this did not… Read More Delete last 5 characters in a string

Advertisements I am trying to declare a list of integers as a variable named list_of_ints. My code works, but it seems to be a bit of a workaround: DECLARE list_of_ints ARRAY <INT64>; set list_of_ints = ( SELECT ARRAY_AGG( DISTINCT some_ints) FROM (SELECT * FROM UNNEST ([11,22,33]) as some_ints) ); I was hoping for something along… Read More Bigquery- Declaring an array from list of Integers

Advertisements I have a BigQuery table like below I want to return the rows whose col_1, col_2, and col_3 values are not the same. In the example above, it should return the 2nd and 3rd row (with key ghj-ikl and tgl-pkl). All 3 columns have to have the same values. How can I do that?… Read More Check if columns in SQL table have the same values

Advertisements I tried to replicate an exercise from an online course in my bigquery sandbox, but it gives me an error. Can someone explain to me what is the error meaning and possibly how to fix this query? schema table employees: nome (string), id_departmento (integer), cargo (string) schema table departments: nome (string), id_departmento (integer) SELECT… Read More How to fix beginner SQL INNER JOIN query error

Advertisements First of all, I’m using BigQuery. In my table I have some duplicated rows due to a change in one of the columns (called fmid). When this happens, I have to get the latest record, which is defined by the time (called created_date). The only thing these duplicated records have in common is the… Read More How to correctly get the second row of a partition by

Advertisements I’m new to bigquery and I’m having trouble using bigquery’s navigation functions. I don’t understand how these functions work. I have a table that has this structure: Event_id created_ts 1 2020-01-01 12:00:00 2 2020-01-01 12:01:00 3 2020-01-01 12:05:00 4 2020-01-01 12:06:00 And I would like to calculate the session id which changes after more… Read More Bigquery navigation function calculate id