I need to find the maximum and minimum values for a summed column over table partitions. The inner query is: and this will produce a table like below. SS_STORE_SK D_YEAR D_MOY TOTAL_SALES rank 182 1999 12 60836090 1 182 1998 11 60792623 2 182 2001 10 60615582 3 182 2000 9 60459371 4 18 1998 12 232323 1 18 2001
Tag: snowflake-cloud-data-platform
Default value data type does not match data type for column DATES_DATE
I’m trying to make a table in Snowflake but it is giving me error due to default value. This seems to work fine in Oracle but not Snowflake. Error- SQL compilation error: Default value data type does not match data type for column DATES_DATE Please let me know where I’m going wrong. Answer Please try this one: Or
Create an array in Snowflake
I want to create an array constructed from two timestamps. After that, use this as a range to find the difference between the first element and the second element. The goal is to find the time difference between timestamps within business hours (9 am to 17 pm). What should be the right approach here? Can I create arrays and then
How do I handle exception while converting date
I have a Sales table and a Period table. Sales table Period table I am getting the below error while I join these 2 tables. Below is the sql I used. As per my understanding, since I have given the country filter as NO in the 2nd line, it should first execute the 2nd line and then do the join
How to combine data from different variables together in SQL?
Let’s say I have data like this: All I want to do is collect the most recent non-null value from each column and condense them into a table with each row being an entry. Final result: Here’s what I have but it’s not complete: Answer Related: Equivalent for Keep in Snowflake: It could be achieved with: Output: ARRAY_AGG by default
SQL: Increment ID only for new rows based on the count
Requirement: Generate new ID from the MAX ID for those Name doesn’t exist in the Target table and has count >1 Below is the Source data, The yellow highlighted are new rows, Those with count >1 are incremented with a new ID, and those with count =1 defaults to FM00000001 The expected result is highlighted in yellow in the Target
Snowflake unsupported subquery when using function
This is the function I created: I’m using the ROW_NUMBER function because the RATE_DATE field is actually datetime and so there are multiple records per date. When I call the function by itself, it works fine. However, when I try to use it in a view, I get the unsupported subquery type error. The view works fine without it. Can
Getting all the values in json array in snowflake
I have a JSON array as follows: This Json data is in the field called col1 in my_table { “JSON_DATA”: { “ID”: 1, “j_array”: [“A”,”B”]…
Not able to resolve “Numeric Value ‘-‘ is not recognized” error in Snowflake SQL Query
I’m working on converting the sql query below over to Snowflake, and got the error “Numeric Value ‘-‘ is not recognized” but it did not have a line number. I assume it’s in reference to the various “-1” instances throughout the query but there’s no line referenced so it’s unclear what’s causing the problem. If it’s the various references to
sql to pull values from array
I have values in array like below trying to get a result set like below, Answer I think you can use flatten function :