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) = 'BLAST' ),

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 protocol, category,

sum(amount) as balance,

sum(case when block_timestamp <= current_date - interval '7 days' then amount end) as balances

from AA as a

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

group by 1,2 ),

PE as(select hour, price from blast.price.ez_prices_hourly where token_address = '0xb1a5700fa2358173fe465e6ea4ff52e36e88e2ad' and price > 0 order by 1 desc limit 1),

CC as( select sum(amount) as supply from AA where contract_address = '0xb1a5700fa2358173fe465e6ea4ff52e36e88e2ad' and user in ('0x0000000000000000000000000000000000000000') )

select

'🤖 '|| protocol as "Defi protocol",

'🟡 '|| to_varchar(balance,'999,999,999,999') as "Total BLAST locked",

'💲 '|| to_varchar(balance*price,'999,999,999,999') as "Total BLAST locked ($)",

category as "Defi category"

from BB, CC, PE

order by balance desc