I’ve got a table with lat and lng coordnates, and need to add the distance into a new column called ‘distance’ in Bigquery. table start_lat end_lat start_lng end_lng 41.8964 41.9322 -87.661 -87.6586 41.9244 41.9306 -87.7154 -87.7238 41.903 41.8992 -87.6975 -87.6722 I haven’t a clue how to do it. I saw some examples, but simply couldn’t apply it into this case.
Tag: google-bigquery
Regex to retrieve dataset and table name using dot in SQL BigQUery
I have an input table which has a list of all queries run in Bigquery. I need to create two columns one with the dataset name and other with table name. The queries example is as follows (some could be nested). I need to separate the dataset name and tablename using the dot in the string Queries dataset table select
Google BigQuery: join on an join Alias
i try to perform an join on a table that is the result of an other join. But i recieve the error: Table “join_1” must be qualified with a dataset (e.g. dataset.table). I bolded the Problem. Thanks a lot in advance. How can I qualify table join_1? I edited the table, I hope that helps to understand the problem more.
A better way to get the last character
I have been working in an exercise, I have 2 values (one with 9 characters and the other with 8) and I have to show 3 columns. The original number The number without the last character Only the last character The Query works but I would like to know a way to improve how I get the last column, I
How to get cumulative total users but ignoring the users who already appear in previous day? using bigquery
So I want to calculate cumulative users per day but if the users exist is previous days they will not counted. on daily basis we can get if we simply calculate cumulative we can get 2,4,6,8 for each day the goal is to get the table like this im using this query to get the result, since the data is
BigQuery: JOIN on single matching row
I have two tables, one containing orders with a nested line_items structure and another with a pricing history for each product sku code. Orders Table order_id order_date item_sku item_quantity item_subtotal 1 2022-23-07 SKU1 7 12.34 SKU2 1 9.99 2 2022-12-07 SKU1 1 1.12 SKU3 5 32.54 Price History Table item_sku effective_date cost SKU1 2022-20-07 0.78 SKU2 2022-02-03 4.50 SKU1 2022-02-03
BigQuery finding sessions that have visited both pageA (contains keyword “main”) and pageB (contains keyword “side”)
On BQ I’m trying to find sessions that have visited both pageA (URL contains keyword “main”) and pageB (URL contains keyword “side”), and the pages that session visited. Here is my logic, I first wanted to find out sessions that have visited pageAs (URL contains keyword “main”), then I wanted to do a join, so to find out those sessions
How to calculate rolling timestamp sum from table partitioned by specific column? – SQL
I have a table with a series of timelines that are normalized starting from 00:00:00.00000. I want to summate them sequentially and stitch them together based on my order_key value. Sample Data: Desired Output: My Attempt: Answer Consider below query: Recursive Approach Non-recursive Approach
SQL query – What does taking the MIN of this boolean expression mean?
Excuse my ignorance about this… I’m taking a data analysis course and I stumbled upon this query in an exercise: ActivityDate is a field that contains date type data and DATE_REGEX is a regular expression variable for a date format string. What I don’t know, is what does taking the MIN() of this boolean expression REGEX_CONTAINS do or mean. I
Starting and Ending a row-count based on values in another column
There is a need to monitor the performance of a warehouse of goods. Please refer to the table containing data for one warehouse below: WK_NO: Week number; Problem: Problem faced on that particular week. Empty cells are NULLs. I need to create the 3rd column: Weeks on list: A column indicating the number of weeks that a particular warehouse is