select

date_trunc('week', day) as "Weeks",

sum(earnings) as "Total Earnings (RUNE)",

sum(block_rewards) as "Block Rewards (RUNE)",

sum(liquidity_fee) as "Liquidity Fee (RUNE)",

sum(bonding_earnings) as "Node Earnings (RUNE)",

sum(liquidity_earnings) as "Liquidity Provider Earnings (RUNE)",

sum(liquidity_earnings) / sum(liquidity_fee) as "Block Emissions Ratio"

from thorchain.block_rewards

where day >= '2022-01-03'

and day <= '2022-10-02'

group by "Weeks"

order by "Weeks"

),

weekly_swap as (

select

date_trunc('week', block_timestamp) as "Weeks",count(distinct tx_id) as swaps,

sum(to_amount_usd) as "Swap Volume (USD)",

sum(liq_fee_rune_usd) as "Liquidity Swap Fee (USD)"

from thorchain.swaps

where block_timestamp::date >= '2022-01-03'

and block_timestamp::date <= '2022-10-02'

group by "Weeks"

order by "Weeks"

select

"Weeks",

"Total Earnings (RUNE)",

"Liquidity Fee (RUNE)",

"Block Rewards (RUNE)",

"Block Emissions Ratio",

"Swap Volume (USD)",

"Liquidity Swap Fee (USD)",

"Swap Volume (USD)" * "Block Emissions Ratio" as "Additional Swaps Volume Required (USD)", swaps* "Block Emissions Ratio" as additional_swap_count_required