outtable AS (SELECT * FROM thorchain.outbound_events), --For convenient reference.

gastable AS (SELECT * FROM thorchain.gas_events), --For convenient reference.

tokengasses AS

( --Gas reimbursement for each external transaction, associated with transaction token type (not gas type).

SELECT outtable.block_timestamp, outtable.block_id,

TO_CHAR(SPLIT(outtable.asset, '-')[0]) AS asset,

-1 * POWER(10,-8) * gastable.rune_e8 / gastable.tx_count AS inflow

FROM outtable INNER JOIN gastable

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

--Keep in mind that 'gastable' will only have asset in terms of the gas asset, whereas 'outtable' will have them in terms of the tokens.

--(A single gas reimbursement may be for multiple token-type migrations.)

--Thus, SPLIT()[0] is necessary.

--Synths have no gas reimbursement and there is no (asset LIKE '%/%') in thorchain.gas_events , so don't split them with '/'.

HAVING inflow <> 0

),

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,

-1 * 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]

)

HAVING inflow <> 0

),