Netezza To snowflake migration of function Percent Rank

danD 0
Viewed 35

We are trying to migrate the SQL queries from Netezza to Snowflake. And got stuck in Percent_rank function of how differently they are used in both. below is the query getting fired in Netezza

Netezza code

Select
percent_rank(x.id,x.rate) within group(order by k.date)
from emp_sales x, emp_order k
where x.inv = k.inv;

Snowflake Converted Code

Select
percent_rank() over (partition by x.id,x.rate order by k.date)
from emp_sales x, emp_order k
where x.inv = k.inv;

However It is throwing error.

k.date is not a valid group by function.

I am stuck in this as not able to figure out how to replicate this. Please suggest.

netezza snowflake-cloud-data-platform
1 Answers

The date/timestamp column should be transformed to integer to be used in this context:

SELECT 
    PERCENT_RANK() OVER (PARTITION BY x.id,x.rate 
                         ORDER BY DATE_PART(EPOCH_SECOND,k.date))
FROM emp_sales x
JOIN emp_order k
  ON x.inv = k.inv;
Lukasz Szozda0
Related