I have two tables t1 and t2 created as follows: Create Tables Goal I am looking to join t2 to t1 if the id of the record exists in the t1 variant array. What I’ve Tried I came across the ARRAY_CONTAINS function which looked perfect. But with the following I am receiving no results: How Do I Get This? Any
Tag: snowflake-cloud-data-platform
Snowflake-SQL – Add row from one query to another query
I’m looking for assistance in combining query results. I have one query that results in: The query: and I have another query that results in: The query: Is there a way to add the TypeACount column to the second query? Answer You can run both queries in a table expressions in a CTE and then join them.
Not less than operator
In snowflake how do I use a not less than operator? If I want to return all rows that are not less than a value? Not sure if I want to use >= and tried !< but doesnt work Thank Answer There no “not less than” operator, but it’s logically the same as “greater than or equal to”: or, if
How to parse a list within a table in Snowflake using SQL
So I have a table as such: I would like to restructure this data so that each list element becomes its own row. If VALUE is an empty list, the expectation is it does not appear in the final dataset. Final result should be: I think using FLATTEN() in Snowflake should work here but I cant seem to get it.
Remove characters after space in sql
I have two tables as shown below: I would like to match these two columns i.e FIRST_NAME from table1 to FIRSTNAME from table2. I tried with the below query but only could match Richard and not Kristin. Please suggest! Answer I would add trim to make sure there’s no whitespace preventing a match.
Count Distinct Window Function with Groupby
I have a table that contains a user’s name, market, and purchase_id. I’m trying to use a window function in SnowSql without a subquery to count the distinct number of purchases a user bought as well as the total number of unique purchases for the market. Initial Table User Market Purchase_ID John Smith NYC 1 John Smith NYC 2 Bob
Snowflake: SQL Query to identify all the different users with the same ID from the same or multiple tables
There are two tables with both IDs and Usernames. Looking for a snowflake SQL query that results in identifying the IDs shared by different users from the same table or both tables. Table 1 ID User 1001 A 1002 B 1003 C 1002 D 1005 E Table 2 ID User 1006 H 1005 E 1003 G 1002 F 1001 A
ETL – Update Tables with potentially new records or updated records
I am trying to accomplish the following table update via an insert for an etl process: Add in a new records that did not exists Update any records that had updates Keep all the records in place that did not change On the table I have a unique_id and an updated_date field when the record was updated ( or could
SQL query to remove duplicates from single column based on latest date
I have a table where I have duplicate values in just one column, and I want to remove those value using the column which has timestamp values. So the value which has latest timestamp should be in the expected result. For example, using the below table column1 is varchar, column2 is timestamp Looking at the above table I have two
How to create custom WEEKOFYEAR index in Snowflake?
I am trying to custom build a week index (1-52) The logic should be like: Start with the 1st Saturday of January of a year Build custom intervals from every Saturday (start of the week) to Fridays (end of the week) and increment the week index by 1. Example: Week 23 will include July 3 2021 – July 9 2021