sql case snowflake-cloud-data-platform window-functions min

snowflake-sql: case when min vs first_value windows function

I hope someone can help. Suppose I have this table

sto_id	pro_id	o_date	d_date
38	111	2021-04-01
30	111	2021-04-10	2021-04-10
30	222	2021-04-15
30	222	2021-04-18	2021-04-19
30	333	2021-04-20

and the result I want to see is

first_sto_id	pro_id	min_o_date	first_d_date
38	111	2021-04-01
30	222	2021-04-15	2021-04-19
30	333	2021-04-20

so if the sto_id is 30 and d_date is null I want so see the next d_date for this pro_id if available. And if sto_id is not 30 then d_date even if it is null.

I tried this:

select 
first_value(sto_id) OVER (PARTITION BY pro_id ORDER BY o_date ASC) as first_sto_id,
pro_id,
min(o_date) OVER (PARTITION BY pro_id ORDER BY o_date ASC) as min_o_date,
case when sto_id='30' then min(d_date) OVER (PARTITION BY pro_id ORDER BY o_date ASC)
else first_value(d_date) OVER (PARTITION BY pro_id ORDER BY o_date ASC) end as first_d_date
from tab1

but I am not getting the result I would need. Here also the fiddle

Any suggestions?? Thanks a lot in advance.

Solution

Using arrays:

-- PostgreSQL
SELECT
 (ARRAY_AGG(sto_id ORDER BY o_date))[1] AS first_sto_id
,pro_id
,MIN(o_date) AS min_o_date
,CASE WHEN (ARRAY_AGG(sto_id ORDER BY o_date))[1] != '30'
           THEN (ARRAY_AGG(d_date ORDER BY o_date))[1]
      ELSE (ARRAY_REMOVE(ARRAY_AGG(d_date ORDER BY o_date),NULL))[1]
      END
FROM tab1
GROUP BY pro_id
ORDER BY pro_id;

db<>fiddle demo

Snowflake version:

SELECT
 (ARRAY_AGG(sto_id) WITHIN GROUP(ORDER BY o_date))[0]::int AS first_sto_id
,pro_id
,MIN(o_date) AS min_o_date
,CASE WHEN (ARRAY_AGG(sto_id) WITHIN GROUP(ORDER BY o_date))[0] != '30'
           THEN (ARRAY_AGG(d_date) WITHIN GROUP (ORDER BY o_date))[0]::date
      ELSE (ARRAY_COMPACT(ARRAY_AGG(d_date) WITHIN GROUP(ORDER BY o_date)))[0]::date
      END
FROM tab1
GROUP BY pro_id
ORDER BY pro_id;