Tag: google-bigquery

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 a…

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…

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 wit…

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:

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:

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’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 the…

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]…