LAG() and LAST_VALUE in snowflake

Question

I have a table that has data looks like:

date	qlfd_lvl	actv_ind	last_value
20230301	4	1	4
20230302	2	1	2
20230303	3	0	2
20230304	1	0	2
20230305	3	1	3
20230306	2	0	3
20230307	1	0	3

we have 2 conditions for column last_value 1.condition: actv_ind=1 then the results will same campaign qlfd_lvl value. 2.condition: actv_ind=0 then results will when previous actv_ind=1 then it will take that date qlfd_lvl value as results

(in snowflake SQL I have tried LAG and last_value but I didn't get logic )

SelVazi · Accepted Answer · 2024-04-05 12:22:06Z

0

You have a gaps and islands problem, you could use the running total to give a distinct ID to each consecutive group of rows (1 followed by zeros), then we can use window function FIRST_VALUE(), to get the desired value :

WITH cte as (
  SELECT *, SUM(actv_ind) over (ORDER BY date) AS sm
  FROM cte
)
SELECT *, FIRST_VALUE(qlfd_lvl) over (PARTITION BY sm ORDER BY date ) AS LAST_VALUE
FROM cte

answered Apr 5, 2024 at 12:22

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LAG() and LAST_VALUE in snowflake

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related