I have data that looks like this (note one TRUE per row): I want to replace each TRUE with a number that depends on the column that it is in (5-1), so a score with very_good being 5 and very_poor being 1, and then squeeze it into one column. So it looks like this: So far I’ve tried this: But
Tag: google-bigquery
Count BigQuery event.params based on event.key
I am attempting to count the events filtering on their parameter. For example, suppose that I have the following. Here’s the real one for the inquisitive minds out there. https://i.imgur.com/rPwmR9i.png event_params.key and event_params.value is an array of values. I need to filter out anything in that array that doesn’t match my key. In addition, I need to count each occurrence
Join two table with Multiple cases in ON condition
I have two tables A and B and I have to perform left join on that with multiple cases in on condition. Is there any efficient way of doing this in big query or SQL. Here my main motive is that for one row if my case1 matches than it will not go into other cases. Likewise it will work
Unique combination of records in a SQL table (Get edges from nodes table)
I came across a use case where I wanted to generate a combination table where the attributes of a record had to be matched with all other records in the table. Something like creating list of edges in a Complete Graph using a nodes table. To give you an example of the requirement, you can consider the following table (Postgres).
Bigquery select records where array column in array value by ‘And’ condition
I have Bigquery table name_fruits like this… I am trying to select names that who has fruits both apple and orange. Which means I want to select aaaa and bbbb. The query needs to be dynamic. Can’t hardcode apple and orange in the query. Is there any solution to select them? Answer If I understand correctly, you want to do
How to calculate weekly and monthly appearances of distinct string values in SQL Google Big Query?
I am new to SQL, and I have a dataset with date values and domain column. Domain column only contains values ‘personal’ and ‘business’. What I am trying to accomplish is to calculate weekly and …
Is there any way i can populate entity based on months but excluding the first month each loops?
i have a set of data sample like this | id | month | x | jan | x | feb | x | mar is there any way i can populate that sample to be like this? | id | month | number | x | jan | 1 | x | feb | …
BigQuery error “Query returned no results”
Or changing the query to this: And other address like this do not pull up the address or amount: Error message: This query returned no results. Answer Just lower it 🙂 Looks like you accidentally changed your address to UPPERCASE or BQ stored it in LOWER case … anyhow if you lowercase the addresses as shown below you’ll start seeing
Calculate Time spent by users on the platform
I’m trying to calculate the time spent by users on my platform on a weekly basis. I have the following api_request_logs table (*Note: each row is duplicate) I tried this query, however, it doesn’t seems it is outputting correct calculation . Current output: Desired output : What I’m doing wrong or do you have a better idea to do it?
inserting data from another project table in BigQuery
I’ve created two table , “conversion_log_” on project1 and “test_table” on project2 in Google bigQuery. I have been trying to select and insert data from conversion_log_ to …