JonasoBlast USDB
    Updated 2024-09-11
    with

    -- 1. Price setup
    PP as(select date(hour) as time, token_address, avg(price) as price from ethereum.price.ez_prices_hourly where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' group by 1,2 ),
    PE as(select hour, price from blast.price.ez_prices_hourly where token_address = '0x4300000000000000000000000000000000000003' and price > 0 order by 1 desc limit 1),

    -- 2. Supply
    AA as(
    select from_address as contract, amount as amount, contract_address, block_timestamp from blast.core.ez_token_transfers union all
    select to_address as contract, 0 - amount as amount, contract_address, block_timestamp from blast.core.ez_token_transfers ),

    BB as(
    select date_trunc('week',block_timestamp) as time,
    sum(amount) as supply,
    sum(case when amount > 0 then amount end) as mint,
    sum(case when amount < 0 then amount end) as burn
    from AA
    where contract_address = '0x4300000000000000000000000000000000000003'
    and contract in ('0x0000000000000000000000000000000000000000')
    group by 1 ),

    TT as( select time from PP where time between (select min(time) from BB) and current_date ),

    CC as(
    select a.time, mint, burn,
    case when supply is not null then supply else 0 end as supply
    from TT as a
    left join BB as b on a.time = b.time )

    select time, 'USDB (Blast)' as token,
    supply, sum(supply) over(order by time) as supply_total,
    mint as "USDB mint",
    burn as "USDB burn"
    from CC
    order by 1 desc