nitsStandardized TVL
    Updated 2022-04-25
    with last_use as
    (SELECT pool_name as pn , max(block_timestamp) as last_use from thorchain.swaps
    GROUP by 1) ,
    asset_prices_fa as
    ( SELECT pool_name,from_asset as a,from_asset_price as a_price
    from
    (SELECT *, from_amount_usd/from_amount as from_asset_price
    , to_amount_usd/to_amount as to_asset_price
    from thorchain.swaps
    inner join last_use
    on pn = pool_name and block_timestamp = last_use )),
    asset_prices_ta as
    ( SELECT pool_name,to_asset as a, to_asset_price as a_price
    from
    (SELECT *, from_amount_usd/from_amount as from_asset_price
    , to_amount_usd/to_amount as to_asset_price
    from thorchain.swaps
    inner join last_use
    on pn = pool_name and block_timestamp = last_use )),
    asset_prices as
    (SELECT pool_name as pn ,a , max(a_price) as final_price from
    (SELECT * from asset_prices_ta
    UNION ALL
    SELECT * from asset_prices_fa )
    where a !='THOR.RUNE'
    GROUP by 1,2 ),
    stake as (SELECT date(block_timestamp) as day, pool_name, sum(rune_e8)/pow(10,8) as total_rune,sum(asset_e8)/pow(10,8) as total_assets
    from thorchain.stake_events
    GROUP by 1,2 ),
    unstake as (SELECT date(block_timestamp) as day, pool_name, (-1)*sum(emit_rune_e8)/pow(10,8) as total_rune,(-1)*sum(emit_asset_e8)/pow(10,8) as total_assets from thorchain.unstake_events
    GROUP by 1,2 ),
    all_data as (SELECT day, pool_name, sum(total_rune) as rune_tvl, sum(total_assets) as assets_tvl, rune_tvl*8 as rune_tvl_usd
    from
    (SELECT * from stake
    UNION ALL
    SELECT * from unstake )
    Run a query to Download Data