WITH

base AS (

SELECT YEAR(day) AS year

, WEEKOFYEAR(day) AS week

, SPLIT(pool_name, '-')[0] AS pool_name

, MIN(day) AS day

, COUNT(DISTINCT day) AS n_days

, AVG((asset_liquidity * asset_price_usd) + (rune_liquidity * rune_price_usd)) AS liquidity_usd

, SUM(COALESCE(system_rewards_usd, 0)) AS system_rewards_usd

, SUM(COALESCE(total_swap_fees_usd, 0)) AS total_swap_fees_usd

FROM thorchain.daily_pool_stats

GROUP BY 1, 2, 3

ORDER BY 1 DESC, 2 DESC, 3

), w AS (

SELECT year

, week

, MIN(day) AS day

FROM base

GROUP BY 1, 2

),

intermediate AS

(

SELECT w.day

, pool_name

, total_swap_fees_usd

, system_rewards_usd

, n_days