I am doing a query on Google Big query, I have joined the 2 tables and created a new column “total gmv” using “SUM” to represent the total revenue, now I wanted to show only the top 2 vendors , GROUP BY country in my query. I manage to show total_gmv group by COUNTRY and vendor_name, but I would like
Tag: google-bigquery
Adding a column to an SQL table and exploding the rows with a set of fixed values for that column
I would like to add a column to an SQL table with unknown columns and explode the entries in that table by a set of fixed values for that column. E.g. Turn unknown col 1 … unknown col x 1 … foo 2 … bar into unknown col 1 … unknown col x new col 1 … foo 1 2
Bigquery replace value in a column based on previous year date condition
I need help to write a SQL logic in bigquery to replace a value in one column based on a condition as below FINANCIAL YEAR –> JUL to JUN How can I make it more dynamic so it rolls over next year Current: Version DATE ACT 202104 ACT 202101 ACT 202006 ACT 202001 ACT 202007 ACT 201903 ACT 202008 TAR
Is there any syntactic sugar to using group by columns 1,2,3,…,n in SQL?
I know it isn’t a “good way of doing things”, but we use the following notations in many of our SQL queries : I was wondering if there is an even more succinct way of writing it, e.g. GROUP BY 1-n (Group by col_1 to col_n) Answer Assume you have table with dozen or more columns Consider below template (note
BigQuery, NULL values when joining transactions and currency exchange tables
I need to convert transactions imports over the last 3 years present in a table to €. To do this, I took a separate table with conversion values. So I need to join this two tables, attach the right conversion rate to the first table. The rates table has two fields (valid_from and valid_to) that contain the rates for the
BigQuery: Best way to ORDER BY an arbitrary GROUP BY expression when that expression is not in the SELECT clause?
In the below query, I want to group all orders by the concatenation of firstname and lastname, and order the result set by that concatenation. I do not want to include the concatenation in the SELECT column list. BigQuery returns the following error for the query: ORDER BY clause expression references column [column_name] which is neither grouped nor aggregated It
BigQuery: Best way to ORDER BY an arbitrary GROUP BY expression?
In the below query, I want to group all orders by the concatenation of firstname and lastname, and order the result set by that concatenation. However, BigQuery returns the following error for the query: ORDER BY clause expression references column [column_name] which is neither grouped nor aggregated I can fix this by wrapping the ORDER BY clause in ANY_VALUE: Is
How to query a struct in BigQuery where two key: value pairs matter
I have data like this: And want to filter rows which contain such key:value pairs: color=blue and size=big. So only this one row remains: Answer You can use unnest():
how to ignore the values before and after between the rows in a table
I have a table as like below I have to add a new column to the table as “value_”, which should contains age values for rows between last TRUE i.e row number 4 (highlighted in green color) …
In SQL how to find data rows separated within few seconds of each other
I have a table in which sensor events are recorded. And a random log is selected for testing every hour at a random time. I want to find N events which are within T seconds of each hourly picking. Data looks like this Event 1 and 3 both happened within 4 seconds of each other. I want to find all