c2ctraderNumber of Mints and Burns of Tinyman Pools
Updated 2022-06-09
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 pool as (
select address pool_address, address_name pool_name from flipside_prod_db.algorand.labels
where label = 'tinyman'
),
asset_info as (
SELECT asset_id, asset_name from flipside_prod_db.algorand.asset
),
asset_price as (
select s.asset_id ,price_usd from algorand.prices_swap s
join flipside_prod_db.algorand.transfers t on t.asset_id=s.asset_id
join pool p on p.pool_address=t.asset_sender or p.pool_address=t.tx_sender or p.pool_address=t.receiver
where s.asset_id in (select asset_id from asset_info) and
s.asset_id in (
select asset_id from
(
select asset_id,max(block_hour) from algorand.prices_swap group by asset_id
)
)
),
burn_info as (
SELECT
case
when asset_sender in (SELECT pool_address from pool) then asset_sender
when tx_sender in (SELECT pool_address from pool) then tx_sender
END as pool_address,
t.asset_id, COUNT(*) Nburn, sum(amount) burn_vol,sum(amount*p.price_usd) burn_vol_usd
FROM flipside_prod_db.algorand.transfers t join asset_price p on t.asset_id=p.asset_id
WHERE asset_sender in (SELECT pool_address from pool)
or
tx_sender in (SELECT pool_address from pool)
group by pool_address,t.asset_id
order by Nburn desc
),
mint_info as (
SELECT receiver pool_address,t.asset_id, COUNT(*) Nmint, sum(amount) mint_vol,
sum(amount*p.price_usd) mint_vol_usd
Run a query to Download Data