select *

from avalanche.defi.dim_dex_liquidity_pools

where tokens:token0 = '0xffff003a6bad9b743d658048742935fffe2b6ed7'

or tokens:token1 = '0xffff003a6bad9b743d658048742935fffe2b6ed7'),

depositst as (

select date_trunc ({{Date_Trunc}},block_timestamp) as date1,

initcap(platform) || ' | ' || Pool_name as LP_Name,

sum (amount) as Deposited_Volume,

sum (amount_usd) as Deposited_Volume_USD,

sum (Deposited_Volume) over (partition by lp_name order by date1) as Cumulative_Deposited_Volume,

sum (Deposited_Volume_USD) over (partition by lp_name order by date1) as Cumulative_Deposited_Volume_USD

from avalanche.core.ez_token_transfers t1 join poolst t2 on t1.to_address = t2.pool_address

where contract_address = '0xffff003a6bad9b743d658048742935fffe2b6ed7'

group by 1,2),

withdrawst as (

select date_trunc ({{Date_Trunc}},block_timestamp) as date1,

initcap(platform) || ' | ' || Pool_name as LP_Name,

sum (amount) as Withdrawn_Volume,

sum (amount_usd) as Withdrawn_Volume_USD,

sum (Withdrawn_Volume) over (partition by lp_name order by date1) as Cumulative_Withdrawn_Volume,

sum (Withdrawn_Volume_USD) over (partition by lp_name order by date1) as Cumulative_Withdrawn_Volume_USD

from avalanche.core.ez_token_transfers t1 join poolst t2 on t1.from_address = t2.pool_address

where contract_address = '0xffff003a6bad9b743d658048742935fffe2b6ed7'

group by 1,2),

finalpoolst as (

select coalesce (t1.date1,t2.date1) as date,

sum (coalesce (deposited_volume,0) - coalesce (withdrawn_volume,0)) as KET_Balance,

sum (coalesce (deposited_volume_usd,0) - coalesce (withdrawn_volume_usd,0)) as KET_Balance_USD,

sum (coalesce (Cumulative_Deposited_Volume,0) - coalesce (Cumulative_Withdrawn_Volume,0)) as Cumulative_KET_Balance,

sum (coalesce (Cumulative_Deposited_Volume_USD,0) - coalesce (Cumulative_Withdrawn_Volume_USD,0)) as Cumulative_KET_Balance_USD,

from depositst t1 full outer join withdrawst t2 on t1.lp_name = t2.lp_name and t1.date1 = t2.date1

group by 1),