JonasoBlast USDB
Updated 2024-09-11
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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