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 also has item_id (String) My objective is to get a list of sessions and its prior_difficulty, by joining the
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 extract day you can use
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 within the same
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 an efficient JOIN.” error message. I’ve also tried performing self-joins like
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 length array) or the whole dataset (10,000 records with
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 below query for your requirement: Result: EDIT: Based on your
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 question (Update values in struct arrays in BigQuery), i was able
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 same concept, it