sorry for the newbie questions, just started learning SQL. I have two tables: sessions items sessions table has questions (RECORD, Repeated), and inside questions there’s item_id (String) items table has topics (RECORD, Repeated), and inside topics there’s prior_difficulty (String). items table al…
Tag: google-bigquery
I am getting “Function not found: day at [5:84]”
This query results in the following error message: “Function not found: day at [5:84]” The syntax error in my SQL is also underlined as can be seen in this screen shot: Answer As mentioned by @Thorsten and @Samuel, you are getting this error as there is no DAY function in BigQuery. If you want to …
how to check if date is within the same month as date in another column SQL BQ
I have two date fields in my BQ table. One is start_date and the other one is end_date. DataType for both columns is: DATE. Data looks like this: I need to create a new column and check if both dates are within the same month. The values would be “yes” and “no”. Yes if two dates are wi…
get rows that have the same value column
I’m trying to select rows that have the same column values using BigQuery on Githubs public data. I’d approach it like so using SQL server but I am getting “Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into…
How to find difference between the integers ignoring null values
Data looks like this Expected Output I am not able to find a way to get the expected data. Answer Consider below approach if applied to sample data in y our question – output is
BigQuery – Solving a basic grouping/ aggregation error
I wish to output a sum of revenue per year per product, but also include a column with the year of introduction of each product (based on date first ordered). I can not figure out what I am doing wrong here as I can do each calculation separately but not it seems within the same query, I am sure this
Unnest Query optimisation for singular record
I’m trying to optimise my query for when an internal customer only want to return one result *(and it’s associated nested dataset). My aim is to reduce the query process size. However, it appears to be the exact same value regardless of whether I’m querying for 1 record (with unnested 48,000…
How can I split a string into two based on a character inbetween using REGEXP_EXTRACT?
I need to split a string into two based on a character and I need to do this without using SPLIT command. What I have: string fruit=orange fruit=apple vegetable=onion What I need: splitstring1 splitstring2 fruit orange fruit apple vegetable onion How can I solve this with REGEXP_EXTRACT? Answer Consider the b…
How do you swap two attributes in array column on Bigquery?
I have a table with two columns: group_id, students (Array of student). Each student have a two attribute: name and last_name. How do you swap name with last_name for each student? I need to swapping students.name to students.last_name, any idea? Answer Finally, thanks to Mikhail Berlyant who answered this qu…
BigQuery JSON – Scalar subquery produced more than one element
I am trying to parse this json in BQ. My desired output is variant helm,chart I have tried the query below and I get the error: Scalar subquery produced more than one element.. If I rewrite this to use a nested arrays, everything works perfect. however, when I try to use a scalar subquery by following the sam…