Snowflake SQL case statement with select and joins

Question

What I would like to do is when there isn't a join to get the fees I want to use the fee value in the fees_calc field from the last fees value/3 found in tablea. Example if the month is 2022-10-01 in tableb and there isnt a quarter available in tablea then join back to tablea and get the max date of the latest record before 2022-10-01 which would be 2022-09-30 end_date. I try a query and I'm getting an error in Snowflake indicting Unsupported subquery type cannot be evaluated.

create or replace table tablea (
key varchar(500),
fees varchar(500),
start_date date,
end_date date
)

create or replace table tableb (
key varchar(500),
asofdate date
)


insert into tablea values ('123','100','2022-07-01','2022-09-30'),('345','200','2022-07-01','2022-09-30'),('123','60','2022-04-01','2022-06-30'),('345','60','2022-04-01','2022-06-30')

insert into tableb values ('123','2022-08-01'),('123','2022-09-01'),('123','2022-10-01'),('345','2022-09-01')

select b.key,b.asofdate,
a.fees,a.start_date,a.end_date,
case when a.fees is null then xxx else fees/3 end as fees_calc 
from tableb b 
left join tablea a on b.key = a.key 
and b.asofdate between a.start_date and a.end_date

Answer 1

You can create a CTE to make sure the tables are joined first, and then calculate the fees. You can use lag with ignore nulls to look back to the previous non-null value within each window frame:

with JOINED as
(
select   b.key
        ,b.asofdate
        ,a.fees
        ,a.start_date
        ,a.end_date
from tableb b left join tablea a on b.key = a.key and b.asofdate between a.start_date and a.end_date
)
select *       
       ,case when fees is null then 
           lag(fees) ignore nulls over (partition by key order by start_date, end_date) / 3 
        else fees/3 end as fees_calc 
from JOINED
;

KEY	ASOFDATE	FEES	START_DATE	END_DATE	FEES_CALC
123	2022-08-01	100	2022-07-01	2022-09-30	33.33333333333
123	2022-09-01	100	2022-07-01	2022-09-30	33.33333333333
123	2022-10-01	null	null	null	33.33333333333
345	2022-09-01	200	2022-07-01	2022-09-30	66.66666666667