Yes, we can use window functions in Incorta spark SQL using 'PARTITION BY', refer to the sample SQL below -
Also refer to this useful link - https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html
1) Example 1 using LAG(), compares current and previous month numbers.
SELECT X.MONTH_SEQ,X.MONTH_YEAR,X.ITEM_NUMBER,
CASE WHEN X.SALES_AMT > 100 AND X.PREVMONTHAMT > 100 THEN 'EXISTING'
WHEN X.SALES_AMT > 100 AND X.PREVMONTHAMT < 100 THEN 'NEW'
WHEN X.SALES_AMT < 100 AND X.PREVMONTHAMT < 100 THEN 'INACTIVE'
END PROD_STATUS,
X.SALES_AMT,
X.PREVMONTHAMT
FROM
(SELECT S.MONTH_SEQ,S.MONTH_YEAR,S.ITEM_NUMBER,S.SALES_AMT,
LAG(S.SALES_AMT,1,0 ) OVER ( partition by S.ITEM_NUMBER ORDER BY S.ITEM_NUMBER,S.MONTH_SEQ) AS PREVMONTHAMT
FROM test.SAMPLE_SALES S
)X
2) Example 2 for computing the end date using LEAD()
SELECT X.PERSON_ID,
X.EFFECTIVE_START_DATE,
coalesce(date_sub(X.EFFECTIVE_END_DATE,1),TO_DATE('2099-01-01','yyyy-MM-dd')) EFFECTIVE_END_DATE,
(case when X.EFFECTIVE_END_DATE IS NULL then 'Y' else 'N' end) current_flag
FROM
(SELECT
PERSON_ID, EFFECTIVE_START_DATE,
LEAD(EFFECTIVE_START_DATE,1) OVER ( partition by PERSON_ID ORDER BY PERSON_ID, EFFECTIVE_START_DATE) AS EFFECTIVE_END_DATE
FROM
EBS_HR_COMMON.PER_ALL_PEOPLE_F
WHERE 1=1) X
.png "Employee"){kind=icon}
