I have a database with two columns as shown below, ID and COUNT_OF_ACTIONS where I want to return the top 1% of values from the COUNT_OF_ACTIONS – below might not be the best example, but imagine COUNT_OF_ACTIONS going from 0 to 100, so the query should return: 99 and 100 since these counts are >= 99 (top 1% value) I
Tag: snowflake-cloud-data-platform
Parametrizing values to a snowflake query from another table
I’m trying to parametrize an update query reading values from a snowflake table as below. Snowflake query : srcTable : The values of col_name in above table – franchise, acct_type & pref_lang are actual columns of the tgtTable which Im trying to update based on old_val and new_val of srcTable in the above Snowflake query. But I’m running into SQL
Trying to split one column to multiple columns using Snowflake sql
data need to get split from SOID column to Circ,Language,Words as show above in pic. When trying with below logic :- the data is getting handled correctly, But as per the pic highlighted in yellow some data is not getting captured with the above logic. Instead of shoing language as English, it is shwing null, and circuit as Biotechnology…. it
Parse JSON like VARCHAR Column in Snowflake
I have a VARCHAR column called TAG within a SQL database with the data looking like the example below. Example: Since it is not a JSON column, I am having difficulty parsing through the column to get the postScore value. I have tried to convert this from a VARCHAR to Variant datatype so that I can do something like the
Unable to convert string to Timestamp
I’m trying to convert a sample 12 hour date with AM/PM into a timestamp, however, Snowflake is throwing an error that it’s not parsable: SELECT TO_TIMESTAMP(‘7/16/2021 4:52:25 AM’, ‘MM/dd/yyyy HH12:mm:ss AM’) The error message returned: Can’t parse ‘7/16/2021 4:52:25 AM’ as timestamp with format ‘MM/dd/yyyy HH12:mm:ss AM’ I’ve tried the hours as both HH and HH12 to no avail. Answer
Checking to see if array field is null or empty?
In snowflake, how can I filter for null or empty array fields in a column? Column has an empty [] or string of values in that bracket, tried using array_size(column_name, 1) > 0 but array_size does not function, Thanks Answer Are you trying to filter them in or out? Either way the array_size should work. although there’s no second argument
Using where clause within a partition
I have huge pageview level table, where each session has multiple pageviews. Session -> Pageviews – 1 : M From the pageviews table, I need to consolidate the data into one row per session with meta data like FirstPageview, LastPageview, #ofvisitstoLogin Firstpageview is defined as the very first pageview for a session (this can be login) Lastpageview is defined as
Snowflake SQL Filter by transactions in the last rolling 30 days
I have a data table something similar to having a customer ID and an item purchase date as shown below. As a filter, I want to return customer ID IFF a given Customer ID has at least 1 purchase in the last 30 rolling days. Is this something that can be done with a simple WHERE clause ? For my
Converting STUFF + FOR XML from SQL to Snowflake
I can’t quite figure out how to convert this to snowflake as I am still trying to learn it, is there a STUFF equivalent? Thanks Answer It is LISTAGG: STUFF + FOR XML is an old workaround used for version of SQL Server that does not support STRING_AGG natively. The Snwoflake equivalent is LISTAGG function that could work either as
Case statement with division
I have case statement below as but am getting error on syntax error, is there proper way to divide in case statement? thanks Answer You need to add quotes to the desired column name (if it’s not going to follow traditional naming rules. Change: To: Btw, the exact syntax error you got was Syntax error: unexpected ‘[‘. (line 7). Please