I need some help cleaning my data. I have a table like below: session_id find_term 11111 null 11111 candy 11111 chocolate 22811 null 11145 null 333222 dark chocolate When session_id has both null and valid find_term, I want to keep the records that only have valid find_terms (eg: for session_id 11111, I want …
Tag: google-bigquery
How to consecutively count everything greater than or equal to itself in SQL?
Let’s say if I have a table that contains Equipment IDs of equipments for each Equipment Type and Equipment Age, how can I do a Count Distinct of Equipment IDs that have at least that Equipment Age. For example, let’s say this is all the data we have: equipment_type equipment_id equipment_age Scre…
ROW type/constructor in BigQuery
Does BigQuery have the concept of a ROW, for example, similar to MySQL or Postgres or Oracle or Snowflake? I know it sort of implicitly uses it when doing an INSERT … VALUES (…) , for example: Each of the values would be implicitly be a ROW type of the Inventory table, but is this construction all…
Extract data from JSON column
i want to extract a value from a json column. The schema is (- first level, — second level): Currently i extract a value this way: Is there a better way to handle the task? Answer Assuming that: event_params is an array of struct type. user_id is a unique key in each event_params Following code style wo…
Nested array in a SQL query
I’m fairly new to programming and I’m struggling with an SQL Query. I want, that the purchased articles (article_id) are grouped in an array, so they are connected to the date column. For a further process, they might be combined ( so date x is combined with articles_id [A,B,C]) To make it clearer…
How to find employee department changes?
I have a table that has the old departments and new departments of various employees: EmployeeID OldDept NewDept 123 Design Design 234 Software Engineering 345 Design Software 456 Advertising Software How can I create a table that looks like this: Dept TransfersIn TransfersOut Software 2 1 Design 0 1 Advertis…
selecting columns which are NOT of a specific type SQL
I want to select all the columns in BigQuery that are not of the type “TIMESTAMP”. I have written the query which returns such columns which is: But I am struggling to return data from only these columns in SQL, I have tried the following query which results in “Scalar subquery produced more…
keep only parent and corresponding child rows linked by parent case ID
I have a table of cases where child cases are linked to parent cases by a parent_case_id field that holds the case_id of the parent case. Cases with null parent_case_id are parent cases or cases that do not have child cases. I would like to remove cases from this table that do not have child cases and keep on…
How to get the minimum value for a given time-period
I have a table with equipment failure and resolved date. Until the failure is resolved, entries for each day will show as failed. Once the issue is resolved data will start from the next failure date. Below is an example I want an output which will give me the first failure time for each resolved timestamp li…
Join two SQL/BigQuery tables that do not contain the same columns
I have two queries that create two separate tables, pulling on several foreign keys. Simplified and using retail food as an example, Table 1 looks like this: Transaction ID City Store Item Description 12320 Boston Joe’s Shop 12305 Banana 12321 LA Bob’s Market 12306 Apple 12322 NY Suzy’s Corn…