SELECT

DAY,

'Block Reward' AS type,

BLOCK_REWARDS_USD AS daily_earnings_usd

FROM

thorchain.defi.fact_daily_earnings where day between '{{START_DATE}}' and '{{END_DATE}}'

UNION ALL

SELECT

DAY,

'Liquidity Fee' AS type,

LIQUIDITY_FEES_USD AS daily_earnings_usd

FROM

thorchain.defi.fact_daily_earnings where day between '{{START_DATE}}' and '{{END_DATE}}'

),

weekly_earnings AS (

SELECT

DATE_TRUNC('week', DAY) AS week_start,

type,

SUM(daily_earnings_usd) AS weekly_earnings_usd,

COUNT(DISTINCT DAY) AS days_in_week

FROM

earnings_by_day

GROUP BY

DATE_TRUNC('week', DAY), type

),

f AS (

SELECT

type,

SUM(daily_earnings_usd) AS total_earnings_usd,

COUNT(*) AS total_days,

AVG(daily_earnings_usd) AS avg_daily_earnings_usd,

FROM