I am more of beginner with sql but would like some help on which statement would be best to use for my query. So I have an app that has test data, because the score could be 90 or be 85.6 the values are in different columns – former in int.value, latter in double.value. I need to merge the two
Tag: google-bigquery
How to fetch data from first row to a particular row
I have two tables table 1 (log_audit table) table 2 is (final_log table) table 1 data looks like below table 2 data looks like below Here user “D” actual resolver of the customer ticket. …
How to GROUP BY first column and choose string from second one depends on number in third one?
I have a problem with GROUP BY one column and choose second column that is string depends on Count number from column three. So I have a table with ID’s in column one, string in column two and Count in column three. I have ordered that by ID’s and Count descending. Most of the ID’s are unique but sometimes id’s
Big Query – Calculate start and end date back to back
I have a problem, which I need some advise, I am required to calculate the number of leave calendar days taken back-to-back on big query. (For eg. 2 leave records taken on 07-01-2020 to 10-01-2020 and 13-01-2020 to 15-01-2020, should return 07-01-2020 to 15-01-2020) However, there are certain weeks, where leave is taken at 3/4 days gap because there is
Add datetime column with values based on another datetime column
I have a table: Using SQL language (BigQuery dialect) I need to add one column date_today_max, such that it copies all data from date column, but for records with the latest date (meaning max(date)) it will replace date with current_date: with Python+Pandas I’d achieve similar with but I have no clue how to tackle this with SQL. There is a
How to fill irregularly missing values with linear interepolation in BigQuery?
I have data which has missing values irregulaly, and I’d like to convert it with a certain interval with liner interpolation using BigQuery Standard SQL. Specifically, I have data like this: # data is …
Order count per ID and calculate time between orders in BigQuery
I am working with customer purchase data and am trying to write a query in Google BigQuery that sorts all purchases by date and adds a purchase/order count per customer (order_count). Also, I would like to calculate a time-delay (in days) between the orders of one customer (purchase_latency). My query currently looks like this: The result including “order_count” and “purchase_latency”
SQL (BigQuery) Grouping Runtime Per Day
I have the following data which I want to group into seconds per day in BigQuery. Source Table: +————–+———————+———————+ | ComputerName | StartDatetime | EndDatetime | +————–+———————+———————+ | Computer1 | 2020-06-10T21:01:28 | 2020-06-10T21:20:19 | +————–+———————+———————+ | Computer1 | 2020-06-10T22:54:01 | 2020-06-11T05:21:48 | +————–+———————+———————+ | Computer2 | 2020-06-08T09:11:54 | 2020-06-10T11:36:27 | +————–+———————+———————+ I want to be able to visualise the data
Find sum of engagement_time_msec for users who have done an event named “yt_event” in BigQuery
My table looks like this: There’s one more column named “user_pseudo_id” which is unique id for users. I want to take sum of event_params.key = ‘engagement_time_msec’ for user_pseudo_id who have done event_name = ‘yt_event’. Also, event_params.key = ‘engagement_time_msec’ is only present in two events only, i.e. event_name = ‘user_engagement’ and ‘screen_view’. I have tried subqueries like this: But I am
BigQuery – JOIN on two tables using string and array
We have two tables in BigQuery like below: Table A Name | Question | Answer —–+———–+——- Bob | Interest | a Bob | Interest | b Sue | Interest | a Sue | Interest | c Joe | …