I have a table with ID, Date, Value. For each row, I want to return the most recent non-null value where Date <= Date minus 1 year. I’m trying variations of window functions with last_value or lag, and partitioning on ID, order by Date, with the IGNORE NULLS clause. However, I’m not sure where to include the condition where Date
Tag: snowflake-cloud-data-platform
SQL: Grouping Paired Rows Without Field to Aggregate On
So say I have the following: Timestamp Event_Name 10:47:00 RESUME 10:37:52 SUSPEND 10:26:20 RESUME 09:48:27 SUSPEND 09:39:13 RESUME 09:15:38 SUSPEND 09:11:42 RESUME I want to be able to pair them as such: Suspend Resume null 10:47:00 10:37:52 10:26:20 09:48:27 09:39:13 09:15:38 09:11:42 If each pair had another field to group on I would use a listagg window function but I
How to write FILE_FORMAT in Snowflake to Java code?
I am trying to execute COPY INTO statement in Java code like this: And it works fine. Is there any way to add this file_format in Java code, so there is no need to set it up in Snowflake? For example, SQL code of file_format that I have set in Snowflake is Is there any way to write this as
Snowflake – Issues with table filtering on dates
I’ve created a query that produces a set of dates based on start and end date. Everything works fine, but when I try to filter the underlying table to only produce the records between my range, it still results with all the underlying dates. I’ve tried using between, changing the variables to dates using to_date(), nothing worked. Any ideas? Answer
REGEXP_REPLACE Strings Starting and Ending with Specific Substrings in Snowflake
I am trying to create a column in a view in Snowflake that replaces any string between strings that I care about with nothing. This is essentially for the purpose of stripping html formatting out of text. As an example: Would should end up like this: Based on the patterns I am seeing, I think that if I can eliminate
How to find out which Foreign Keys to create / reference in existing tables in Snowflake?
We have a lot of tables in Snowflake, none had Primary Keys (I’ve just added them in) but now I’m feeling overwhelmed trying to figure out how to create Foreign Keys without going through each table to see the link between data in two tables? Is there a script that I can create to even do this? P.S. Below is
Valid_from Valid_to from a full loaded table
There is a source table which loads the data full and monthly. The table looks like below example. Source table: pk code_paym code_terms etl_id 1 2 3 2020-08-01 1 2 3 2020-09-01 1 2 4 2020-10-01 1 2 4 2020-11-01 1 2 4 2020-12-01 1 2 4 2021-01-01 1 2 3 2021-02-01 1 2 3 2021-03-01 1 2 3 2021-04-01
Is SQL floating point sum affected by the order-by clause?
Unlike the pure mathematical real numbers, or even the rational numbers, the floating-point number types are not commutative-associative. This means, as is commonly known in numerical coding, the order of a list of floating-point numbers affects the value of the floating-point sum. It can be a good idea to sort the list to put the smaller numbers first before adding
Snowflake unable to display / interpret unicode ‘u0089’
I am trying to show Unicode character ‘u0089’ in the snowflake browser results, however it seems to be showing a default error value instead. I’m lost as to how to fix this issue The data is being ingested from a source JSON doc which states the field to be As you can see, “Units”: “u0089” is not displaying correctly as
Fetch data from XML – XMLGet function using with Attribute Name and its value in Snowflake
I have an xml which I need to read using Snowflake SQL. I need to use the attribute name and lits value to fetch the data. For example -> id and “bk101” to fetch the contents. I am currently using below query- In this case I am passing the index 0-4 to fetch the data is not producing correct results.