detailstable AS (SELECT '2022-05-13' AS hajimari, '2025-01-01' AS owari),

--Note, since involving outbound observations, do not start earlier than block 5531994 (2022-05-12) (v1.88.1, Merge Request 2221).

outtable1 AS (SELECT block_id, fact_outbound_events.* FROM thorchain.core.fact_outbound_events INNER JOIN thorchain.core.dim_block ON fact_outbound_events.dim_block_id = dim_block.dim_block_id), --For convenient reference.

gastable1 AS (SELECT block_id, fact_gas_events.* FROM thorchain.core.fact_gas_events INNER JOIN thorchain.core.dim_block ON fact_gas_events.dim_block_id = dim_block.dim_block_id), --For convenient reference.

outtable AS (SELECT outtable1.* FROM outtable1, detailstable WHERE DATE(block_timestamp) BETWEEN hajimari AND owari),

gastable AS (SELECT gastable1.* FROM gastable1, detailstable WHERE DATE(block_timestamp) BETWEEN hajimari AND owari),

churngasses AS

( --Gas costs for all internal extractions of a gas-type, associated with that gas type (not transaction token type, since not represented)..

SELECT *

FROM (

SELECT gastable.block_timestamp, gastable.block_id,

CONCAT(SPLIT(gastable.asset, '.')[0], '_upkeep') AS asset,

POWER(10,-8) * AVG(gastable.rune_e8) * (AVG(gastable.tx_count) - COUNT(outtable.block_id)) / AVG(gastable.tx_count) AS inflow

--Remember that gastable does not/cannot distinguish between tokens types that gas is being paid for.

--Also remember that there should be no synth-type gas reimbursements. (No '/' split appropriate in e.g. v1.87.1.)

FROM gastable LEFT JOIN outtable

ON (outtable.block_id = gastable.block_id) AND (SPLIT(outtable.asset, '.')[0] = SPLIT(gastable.asset, '.')[0])

GROUP BY gastable.block_timestamp, gastable.block_id, SPLIT(gastable.asset, '.')[0]