to_rest_of_network AS (

SELECT block_timestamp, DATE(block_timestamp) AS date,

dim_block_id,

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

-1 * POWER(10,-8) * rune_e8 AS rune_amount

-- This takes into account block rewards sent to pools and partial liquidity fees sent out of pools.

-- Here, treating 'the network' as the Reserve and node operators,

-- not addressing by that liquidity fees from the pool all go to node operators

-- while block rewards to pools come from the Reserve,

-- other block rewards also going from the Reserve to node operators.

FROM thorchain.core.fact_rewards_event_entries

--WHERE pool IN ('BTC.BTC', 'BNB.BNB', 'ETH.ETH', 'BNB.BUSD', 'ETH.USDC')

),

daily AS (

SELECT DISTINCT block_timestamp, date, dim_block_id, --pool,

SUM(rune_amount) OVER(PARTITION BY date/*, pool*/) AS rune_amount

FROM to_rest_of_network

QUALIFY block_timestamp = MAX(block_timestamp) OVER(PARTITION BY date/*, pool*/)

),

cumulative AS (

SELECT date, dim_block_id, --pool,

SUM(rune_amount) OVER(/*PARTITION BY pool */ORDER BY date ASC) AS cumulative_rune_amount,

COUNT(*) OVER(/*PARTITION BY pool */ORDER BY date ASC) AS pool_days,

cumulative_rune_amount / pool_days AS cumulative_rune_amount_per_pool_days

FROM daily

SELECT date, block_id,

--pool,

cumulative_rune_amount, pool_days, cumulative_rune_amount_per_pool_days

FROM (cumulative INNER JOIN thorchain.core.dim_block ON cumulative.dim_block_id = dim_block.dim_block_id)

ORDER BY date DESC, cumulative_rune_amount_per_pool_days DESC