SELECT

TRUNC(day, 'week') AS week,

SUM(bonding_earnings) AS weekly_bonding_earnings,

SUM(liquidity_earnings) AS weekly_liquidity_earnings

FROM thorchain.defi.fact_block_rewards

GROUP BY TRUNC(day, 'week')

),

weekly_tvl AS (

SELECT

TRUNC(day, 'week') AS week,

AVG(total_value_bonded) AS avg_total_value_bonded,

AVG(total_value_pooled) AS avg_total_value_pooled

FROM thorchain.defi.fact_daily_tvl

GROUP BY TRUNC(day, 'week')

SELECT

wr.week AS week,

(wr.weekly_bonding_earnings / wt.avg_total_value_bonded) * 100 * 52 AS APY_nodes,

(wr.weekly_liquidity_earnings / wt.avg_total_value_pooled) * 100 * 52 AS APY_LPs

FROM weekly_rewards wr

LEFT OUTER JOIN weekly_tvl wt

ON wr.week = wt.week

where wr.week<trunc(current_date,'week') --and wr.week<'2024-07-29'

ORDER BY wr.week ASC