0xaimanAverage Weekly ROI on Thorchain of LPs
    Updated 2021-10-26
    with total_pool_rewards_tbl AS (select date_trunc('week', block_timestamp) as Week , sum(bond_e8) as bond_earning
    from thorchain.rewards_events
    group by week),


    tvl_pool1 as (select week_bpd, sum(tvl) as tvl
    from

    (select date_trunc('week',block_timestamp) as Week_bpd,
    avg(rune_e8) as tvl, pool_name
    from thorchain.block_pool_depths
    group by Week_bpd,pool_name
    order by tvl desc
    )
    group by week_bpd)

    select total_pool_rewards_tbl.Week , bond_earning/pow(10,8) as bond_earnings, tvl/pow(10,8) as tvl, bond_earning/tvl * 100 as Weekly_APY
    from total_pool_rewards_tbl
    Left join tvl_pool1
    on total_pool_rewards_tbl.week= tvl_pool1.week_bpd




    Run a query to Download Data