I have a dataset in bigquery which contains order_date: DATE and customer_id. How can I count distinct customer_id between the months of the previous year and the same months of the current year? For example, from 2020-01-01 to 2021-01-01, then from 2020-02-01 to 2021-01-01, and so on until the current date and should be grouped by the latest date. The
Tag: google-bigquery
Create a Month function
I would like to run a query that runs monthly but it will check daily if the timestamp from the records is from the previous month. If yes then it will run the query and append to the previous monthly data. So, I want to create a simple function to return TRUE if the RecordTimestamp is from the previous month.
Translating Oracle Date Functions to BigQuery
I’m having trouble translating these Oracle date functions to BigQuery because BigQuery doesn’t allow such user-friendly additions to dates/timestamps. Any help is appreciated. Below are the two date functions in Oracle and my attempted (failed) versions in BigQuery: Oracle: BigQuery attempt: Answer You can use below for the first one and for second – try below
BigQuery/SQL: Split String based on the second dot from right
update: there are situations that dot position that might not be the best solution. I got a column of website. I want to transform it into Anyone knows how to split based on the ‘.’ position from the right? Thanks. Answer regexp_substr() does exactly what you want:
Remove duplicated rows with same Timestamp but different values
I have “duplicated” rows in Bigquery and I need to keep just the last occurrence grouped by id of element. As you can see, these are not duplicated rows, those are duplicated Timestamps with different values. I need to keep one registry per Timestamp. I run this query to get the example: Table with data example: Answer In your sample
Thousands separator in sql with dot in bigquery
How to cast numeric type let say 30000 into string type with dot as thousand separator 30.000. I tried to use But the result is 30,000 not 30.000 Answer You could just replace the , with . after formatting:
Using SQL result as a filter for another query
Here’s my code and there’s a thousand transaction_no result. Which is I have to use as a filter for another code with the same table. Answer You could use in, if you want to filter on the transactions: If you want all rows for transactions that have the specified item, you can also use qualify:
How to retrieve last year data on line-by-line basis (main set grouped by year, month & aggregated on volume)?
Is there a way to easily retrieve last years data during volume aggregation, grouped by year, month. Sample of code below (from BQ). It shows an error in the subquery WHERE clause expression references t1.date which is neither grouped nor aggregated Answer If you have data every month, then you can use lag(). I would recommend using date_trunc() instead of
Combining two queries with case statement in BigQuery
I’m new to SQL and have been trying to combine two queries that give me a count of unique uses by day of the week (‘weekday’ – with days of the week coded 1-7) and by user type (‘member_casual’ – member or casual user). I managed to use a case statement to combine them into one table, with the following
How can I translate this legacy SQL to standard SQL in BigQuery?
I need to translate this to BigQuery. Can anyone help? Thanks This is the error I’m getting. The Google BigQuery Standard SQL database encountered an error while running this query. Query execution failed: – Syntax error: Expected “(” but got identifier “DATEDIFF” at [2:25] Answer Try this (docs):