c2ctraderNumber of Mints and Burns of Tinyman Pools
    Updated 2022-06-09
    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