I’ve seen a lot of questions about this error, the closest one (as it’s using window function) to my issue would be this one My issue is that I use columns in the windows function that are not …
Tag: snowflake-cloud-data-platform
Cohort Analysis using SQL (Snowflake)
I am doing a cohort analysis using the table TRANSACTIONS. Below is the table schema, Below is a quick query to see how USER_ID 12345 (an example) goes through the different cohorts based on the date filter provided, The result for this query with the time frame (two weeks) would be and this USER_ID would be …
How to do a join to get previous days values?
I want to join tables as follow: Report_Period Entity Tag Users Count Report_Period_M-1 Report_Period_D-1 … 2021-03-31 entity 1 X 471 2017-05-31 2021-03-18 … 2020-12-31 entity 2 A 135 2020-11-30 2021-03-18 … 2020-11-30 entity 3 X 402 2020-10-31 2021-03-18 … With a view containing Day &…
Snowflake how to split a field having values separated with commas and add them separately to the target table each value in a single row?
I have a field that might hold values as follows: field_val = ‘Val1, Val2’; So using merge into command, I need to split these values and add them separately each as a single row. In that case, I don’t want them added row to be: 23, ‘Val1, Val2’ What I want is to add the separate…
Keep multiple rows during PIVOT in Snowflake
I would like to transpose rows into columns in Snowflake. Suppose I have the following table BASE ID value type 1 100 ‘A’ 1 200 ‘B’ 1 300 ‘B’ 2 400 ‘A’ The output should be as follows: ID A B 1 100 200 1 100 300 2 400 NULL Currently I am pivoting the table with …
SQL date scenario for getting previous month’s data to-date on the 1st of month, then drop last month
I’m trying to recreate an existing View in Snowflake (originally in SQL Server). I need to get last month’s data which is easy enough, but here’s the scenario: On 3-1-21, it should retrieve 2-1-21 to 3-1-21, but then starting on 3-2-21, it should only show 3-1-21 to-date, as our month end re…
SQL Snowflake Column Condition Check
Basically, I am trying to write a query against a table such like the following. Here we have Plans of either Free, Plus or Premium (in the example below I only show free and plus plans). Is it possible to return records where an ID that was original a ‘Free’ plan has changed to either a ‘pl…
Snowflake how can we cast all fields of select query when unpivot is not detecting fields with a different types?
Following up on this question, I made the following script: It will help me transpose a temporary table to add all related fields into another table, where each field/value pair represent a row. The error I am getting out of this query is: SQL compilation error: error line 2 at position 77 invalid identifier …
Setting rules for max() with strings
Each record in my table has a random selection of these strings, so it looks like I want to get the highest value of “fruit” for each record, except I want it to be sorted lower if it contains the string guava. This is the output that I’d like and I’m using Snowflake SQL output: Curren…
How to automate a sql script in snowflake
I have a SQL script that transforms data for various tables in snowflake. I need to run this SQL code daily and I want to automate it somehow. I thought I could automate the SQL with ‘tasks’ in snowflake but ‘tasks’ are unable to run more than one single SQL statement and my code conta…