I need to do a query to show the top seller products group by city. The “trap” is, I just can show the top 5 by city: I start using this query: select ORD.shipping_address_city city, ORDL.sku,…
Tag: google-bigquery
Bigquery Full Join ON multiple conditions
I want to perform a Full Outer join on two tables with multiple conditions to result all the matching records along with unmatched records from both tables. Tbl1 is a bigger table with 21 M records and Tbl2 has 5k rows, like the example query below. But the outer join cannot be performed with OR conditions be…
How to run a query for current date for date partitioned tables?
I have a table that is partitioned by date so something like ‘tablename_20191205’. Is there a way for me to run the query everyday without having to modify the date? Also how would I do this if I am using two tables. So instead of typing, Select * From ‘tablename_20191205’as table1 and…
Extract user journey data between two pages in BigQuery Google Analytics data
How to extract users’ journey data between two specific pages in Google Analytics BigQuery Export data? Example: Website has 100 pages: hits.page.pagePath=/page_1 to hits.page.pagePath=/page_100. Goal is to extract user journey data from /page_13 to /page_22, including all in-between pages. The challeng…
Select customers who purchased BOTH this year and last
I’m fairly new to SQL and I’m using BigQuery to find customers who purchased in 2019 and 2018. This is the query I’m using to find the customers who purchased in 2019. SELECT DISTINCT contact_email …
How to transform a BigQuery table into a list of row sequences where sequences are aggregated by a sliding window in time?
I have a very large table where each row represents an abstraction called a Trip. Trips consist of numeric columns such as vehicle id, trip id, start time, stop time, distance traveled, driving duration, etc. I want to transform this table into a list of Trip sequences where Trips are grouped into sequences b…
Big Query data totals are wrong, suspect duplicate rows after LEFT JOIN
I’m trying to join two tables in Google Big Query and I think it’s duplicating a few rows (I’m guessing this because the totals are wrong when I then use the data in Data Studio). To give some …
How to duplicate rows generating dates between Start Date and End Date in BigQuery?
I’m having a question on how to duplicate rows in a table that generates dates between StartDate and EndDate. Actually, I have a same question as this Duplicating records to fill gap between dates in Google BigQuery but with a different type of table. So, my table looks like this: and I would like the o…
SQL: Filling in Missing Records with Conditional
I need to count the number of products that existed in inventory by date. In the database however, a product is only recorded when it was viewed by a consumer. For example consider this basic table structure: Using the following query, I attempt to determine the amount of products in inventory on a given date…
Equivalent of string contains in google bigquery
I have a table like as shown below I would like to create two new binary columns indicating whether the subject had steroids and aspirin. I am looking to implement this in Postgresql and google bigquery I tried the below but it doesn’t work I expect my output to be like as shown below Answer Below is fo…