I am new to SQL. I have a table that has billion of records with multiple columns, let’s say a,b,c,d,e,f. I would like to create a query for number of records for each column with certain condition …
Tag: google-bigquery
Select query to group records in output json using BigQuery
I have a datetime partitioned table in BigQuery with a schema like: So, if I do a SELECT * FROMmy_table`, it will generate a JSON with a structure like: And that format works well in most of my use cases, except for one. So, if I wanted the id’s of all the inverters i would do But I also need
How to join two tables to determine date ranges when one table contains (id, start_date) and another contains (id, end_date)
I’m new to SQL, hope you guys don’t find it silly. Working with two tables here, one contains start dates and other contains end dates. Entries do not follow sequence/possibility of duplicates. Answer You can use union all and aggregation with some window functions:
How to add a column of total number of rows in BigQuery
I have a table in BigQuery where I save a DATESTAMP and temperature sensor value. I usually filter the query by dates. My goal is to add a column in this query where it puts the total number of rows. For example, if I run this query I have this result: And when I run this query I have this
Self-Joining across nested Records in BigQuery
I’m trying to do some joins/aggregations between nested fields in single table and running into both SQL problems and the “Correlated sub queries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN” error. I’d love some SQL help with the general problem, but I’m also curious how to
How would i use a wildcard for a specific string in SQL
I have a string which I need to select in a CASE statement: LLF_RET_mob However, when I use the following string in my LIKE statement in SQL it returns null: %LLF\_RET_\_mob% What would be …
BigQuery SQL: do SELECT aliases have priority over FROM clause column names when used in GROUP BY clause?
I have a simple query that has the ambiguity regarding col: According to the documentation, the query validator should raise an error: Ambiguity between a FROM clause column name and a SELECT list alias in GROUP BY: The query above is ambiguous and will produce an error because LastName in the GROUP BY clause could refer to the original column
Improve SQL query to find range between start and end date
I’m working with a database called international_education from the world_bank_intl_education dataset of bigquery-public-data. My aim is to plot a line graph with countries who have had the biggest and smallest change in Population growth (annual %) (one of the indicator_name values). I have done this below using two partitions finding the first and last value of the year by each
Subquery error calculating Free throw statistics
trying to incorporate the query titled “free throws made” as a subquery into the query below it titled “FreeThrowPercByGame” but getting an error. Both work independent of each other in Google Big …
Standard SQL – How to count frequency of values in array
I get the following Table with the query underneath: For each of the values in the trafic_medium column (e.g.: cpc, referral, organic, etc.) I am trying to figure out how often each value occurred in the array, so preferably add a new column ‘count’ that shows ho often that value occurred? I’m new to SQL so I’m quite stuck. I