LL as(

select to_varchar(value:BB) as category, to_varchar(value:CC) as protocol, to_varchar(value:CT) as CONTRACT

from (select live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/868a10dd-8c47-4df6-a5d5-d246600e5411/data/latest') as db ) , LATERAL FLATTEN (input => db:data)

where to_varchar(value:AA) = 'USDB' ),

AA as(

select block_timestamp, symbol, from_address as user, to_address as pool, amount_usd, amount as amount, contract_address from blast.core.ez_token_transfers union all

select block_timestamp, symbol, to_address as user, from_address as pool, amount_usd, 0 - amount as amount, contract_address from blast.core.ez_token_transfers ),

BB as(

select date_trunc('week',block_timestamp) as time, sum(amount) as balance

from AA as a

join LL as b on a.pool = lower(b.contract) and a.contract_address = '0x4300000000000000000000000000000000000003'

group by 1),

CC as(

select a.*, case when balance is null then 0 else balance end as balance

from (select distinct time from BB) as a

left join BB as b on a.time = b.time),

DD as(

select time, 'USDB on DeFi' as type, balance, sum(balance) over(order by time) as balance_defi

from CC ),

SS as( select sum(amount) as supply from AA where contract_address = '0x4300000000000000000000000000000000000003' and user in ('0x0000000000000000000000000000000000000000') ),

EE as(

select *, rank() over(order by time desc) as rank

from DD