I have two tables in my database, each table has column with names. How to compare these tables columns and how to find these names what has exact matches and these names, which are similar in table1 and table2? For example : Table 1 Table 2 : Result should come out with this: Table 1 –> Table 2 how …
Tag: google-bigquery
Getting a Monthly Date Range
How can you make a date range in a big query? A date range starts from 29th of the month and ends with 28th of the next month. It should be like this Date | Starting Date | Ending Date 03-13-2020 | 02-29-2020 | 03-28-2021 06-30-2020 | 06-29-2020 | 07-28-2021 01-01-2021 | 12-29-2020 | 01-28-2021 11-11-2021 | 1…
BigQuery – Scalar subquery produced more than one element –
I have this query that helps me to find separate key words within strings (very useful with utm_campaign and utm_content): For example: if I have a umt_campaign like this: us_latam_mkt_google_black-friday_audiencie-custom_NNN-NNN_nnn_trafic_responsiv The query from above will help me to separate each word wit…
transpose bigquery records and concatenate the result as a string and and alternative array
I have the following table. I get the following output. How can I make this more efficient. final output 1 The function i used is as follows. CREATE OR REPLACE FUNCTION sp.string_flatten_dedup(string_value string, delim string) AS ( ); I would also like to be able to store the data where concat_last_name as a…
bigquery transpose and concatenate for each record
I want to achieve the following transformation. I have last_name stored in a repeated record as follows. data before transformation I want to achieve the following. data after transformation Example with sample data created. I’m not sure either if I should store it as an array instead of a concatenated …
Unnecessary blank row in SQL query
Aim was to write a SQL query to return ride details from a public database. While going through a data analytics course, I stumbled across this problem. the code used is correct as the instruction provided was the same however the result I obtained was different. The first row returned a blank row with just t…
Select different min dates BigQuery
I have a table with different values in BigQuery and I want to select the min or the max of these values. Answer You can use min/max with group by to get min or max date per ID group
BigQuery – How to select the first campaign each user clicked on?
Let’s imagine that I have a website that sells electronics. In order to attract customers, I invested some money in online ad campaigns. The following table has three columns: user_id (id that identifies each user), event_date (the date when the user clicked on the ad) and mkt campaign (the name of the …
SQL Pivot in BigQuery
I have a SQL table with information about email campaigns that my company has created. Each line of the table is an action that a user has taken on a specific campaign: User ID Campaign Name Status 01 Campaign#1 opened 01 Campaign#1 clicked 01 Campaign#2 opened 02 Campaign#1 opened 02 Campaign#2 opened I want…
SQL query to split and keep only the top N values
I have the following table data: I need to create a table with a split items column, but with the limitation to have at most N items per name. E.g. for N = 3 the table should look like this: I have the following query that splits items correctly, but doesn’t account for the maximum number N. What shoul…