with base as (select date_trunc('week', block_timestamp) as week,

split(pool_name, '-')[0] as asset,

avg(asset_e8/1e8) as savers_depth

from thorchain.core.fact_block_pool_depths

where pool_name like '%/%'

and week is not null

group by 1,2),

base2 as (select date_trunc('week', block_timestamp) as week,

split(replace(pool_name, '.', '/'), '-')[0] as asset,

avg(asset_usd) as asset_price

from thorchain.core.fact_prices

group by 1,2

order by 1 desc),

base3 as (select a.week,

split(a.asset, '/')[1] as assets,

a.savers_depth * asset_price as savers_depth_usd,

sum(savers_depth_usd) over (partition by a.week) as cumulative_savers_depth_usd

from base as a

left join base2 as b

on a.week = b.week

and a.asset = b.asset

where a.week is not null

and a.week >= '2023-04-01'

and a.week <= '2023-06-30')

select *,

(select avg(cumulative_savers_depth_usd) from base3 where week in (select max(week) from base3)) as Q2_ending_savers

from base3

order by 3 desc