alleria7. [Hard] Weekly APY Weekly
    Updated 2021-11-11
    WITH
    liquidity_by_pool_by_date as (
    SELECT
    date(block_timestamp) as dates,
    pool_name,
    avg(rune_e8 / POW(10, 8)) as rune_amount
    --avg(asset_e8 / POW(10, 8)) as asset_amount
    FROM thorchain.block_pool_depths
    GROUP BY date(block_timestamp), pool_name
    ORDER BY dates ASC
    ),

    total_liquidity_by_date as (
    SELECT
    dates,
    sum(rune_amount) as daily_total_rune_amount
    FROM liquidity_by_pool_by_date
    GROUP BY dates
    ORDER BY dates ASC
    )


    SELECT
    dates,
    liquidity_earnings,
    daily_total_rune_amount,
    liquidity_earnings / daily_total_rune_amount as ratio,
    liquidity_earnings / daily_total_rune_amount * 100 * 7 as APR,
    (POW( (1+ratio), 7) - 1) * 100 as APY
    FROM thorchain.block_rewards
    LEFT JOIN total_liquidity_by_date ON total_liquidity_by_date.dates = day
    ORDER BY dates ASC

    Run a query to Download Data