SELECT

pool_name,

date_trunc('week', block_timestamp) as cw,

avg(rune_e8) / POWER(10, 8) as rune_amt,

avg(asset_e8) / POWER(10, 8) as asset_amt

FROM thorchain.block_pool_depths

GROUP BY 1,2

), tab2 as (

SELECT

pool_name,

date_trunc('week', block_timestamp) as cw,

sum(liq_fee_asset_usd + liq_fee_rune_usd) as rewards

FROM thorchain.swaps

GROUP BY 1,2

), tab3 as (

SELECT

date_trunc('week', block_timestamp) as cw,

pool_name,

avg(asset_usd) as asset_usd,

avg(rune_usd) as rune_usd

FROM thorchain.prices

GROUP BY 1,2

), tab4 as (

SELECT

tab1.cw,

tab1.pool_name,

rune_amt * rune_usd + asset_amt * asset_usd as total_lq

FROM tab1

join tab3 on tab1.cw = tab3.cw AND tab1.pool_name = tab3.pool_name

SELECT

tab2.cw,

tab2.pool_name,

((rewards / total_lq) * 52) * 100