--All values used here are in RUNE. WITH 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 ), ILPtable AS ( --ILP payouts. SELECT block_timestamp, block_id, CONCAT(SPLIT(pool_name, '-')[0], '_ILP') AS asset, -1 * POWER(10,-8) * imp_loss_protection_e8 AS inflow --Here, distinguishing by asset is possible. FROM thorchain.unstake_events WHERE (imp_loss_protection_e8 <> 0) ), reserve_eventstable AS ( --Donations to the Reserve. SELECT block_timestamp, block_id, TO_CHAR('reserve_event') AS asset, POWER(10,-8) * asset_e8 AS inflow FROM thorchain.reserve_events WHERE (asset = 'THOR.RUNE') AND (asset_e8 <> 0) ), rewardstable AS ( --Pool rewards summed with bond rewards to get all block rewards; NEEDS WORK re:incentive curve rewards+fees effect. (slash amounts, 'slash_reserve_third', different cases...) SELECT block_timestamp, block_id, TO_CHAR(asset) AS asset, inflow FROM ( ( SELECT block_timestamp, block_id, 'bond_block_rewards' AS asset, -1 * POWER(10,-8) * bond_e8 AS inflow FROM thorchain.rewards_events --Bond reward table. ) UNION ALL ( SELECT block_timestamp, block_id, CONCAT(SPLIT(pool_name, '-')[0], '_block_rewards') AS asset, -1 * POWER(10,-8) * IFF(block_id >= 5199216, rune_e8, IFF(rune_e8 > 0, rune_e8, 0)) AS inflow --Before version 1.87.1 (Merge Request 2199, Issue 1291) PoolDeficit wrongly sent to Bond instead of Reserve. FROM thorchain.rewards_event_entries --Pool reward table (when negative, ideally Pool to Reserve).t ) ) HAVING inflow <> 0 ), feetable AS ( --Outbound fees sent to Reserve. --Careful that the THOR.RUNE outbound fees aren't represented in chain-specific summaries, --as there is no pool_name field, only asset. SELECT block_timestamp, block_id, TO_CHAR(SPLIT(asset, '-')[0]) AS asset, POWER(10,-8) * IFF(asset <> 'THOR.RUNE', pool_deduct, asset_e8) AS inflow --When the outbound fee asset is THOR.RUNE, in v1.87.1 pool_deduct is 0, --even though (since indeed deducted) it should equal asset_e8. --Note block 5423619 and the relevance of refund events for non-0.02 RUNE cases. FROM thorchain.fee_events HAVING inflow <> 0 ), slashtable AS ( /* Slashes paid to reserve. Note that thorchain.slash_amounts ("slash" events) cover only the amounts paid to the pool, whereas 'bond_cost' bond_type events cover many events other than slashes. Until further notice, note that helpers.go slashing of Yggdrasil assets upon node-leaving allocates the full 1.5x to the Pool, in contrast with manager_slasher_current.go 's approach of 1.0x to the Pool, 0.5x to the Reserve. */ SELECT block_timestamp, block_id, TO_CHAR('reserve_slash_third') AS asset, --NOTE, this currently assumes all off-by-one errors to be POWER(10,-8) * amount_e8 AS inflow FROM thorchain.transfer_events WHERE ((from_address = 'thor17gw75axcnr8747pkanye45pnrwk7p9c3cqncsv') AND (to_address = 'thor1dheycdevq39qlkxs2a6wuuzyn4aqxhve4qxtxt')) AND amount_e8 <> 2000000 --All exactly-0.02-RUNE cases are currently assumed to be network fees. ), stagedcosttable AS ( SELECT block_timestamp, block_id, TO_CHAR(SPLIT(asset, '-')[0]) AS asset, POWER(10,-8) * SUM(rune_amount) AS inflow FROM thorchain.pool_balance_change_events WHERE reason = 'pool stage cost' GROUP BY block_timestamp, block_id, asset ), burndusttable AS ( --Dust-burning pool-replenishment upkeep, at time of writing only observed for ETH.ETH. SELECT block_timestamp, block_id, CONCAT(SPLIT(asset, '-')[0], '_upkeep') AS asset, -1 * POWER(10,-8) * SUM(rune_amount) AS inflow FROM thorchain.pool_balance_change_events WHERE reason = 'burn dust' GROUP BY block_timestamp, block_id, asset ), thornamestable AS ( --Dust-burning pool-replenishment upkeep, at time of writing only observed for ETH.ETH. SELECT block_timestamp, block_id, 'thornames' AS asset, POWER(10,-8) * (SUM(fund_amount_e8) + SUM(registration_fee_e8)) AS inflow FROM thorchain.thorname_change_events GROUP BY block_timestamp, block_id, asset ), union_without_network_fees AS ( --Combining everything except network fees first, for network fee preparation. (SELECT * FROM tokengasses) UNION ALL (SELECT * FROM churngasses) UNION ALL (SELECT * FROM ILPtable) UNION ALL (SELECT * FROM reserve_eventstable) UNION ALL (SELECT * FROM rewardstable) UNION ALL (SELECT * FROM feetable) UNION ALL (SELECT * FROM slashtable) UNION ALL (SELECT * FROM stagedcosttable) UNION ALL (SELECT * FROM burndusttable) UNION ALL (SELECT * FROM thornamestable) ), networkfeetable AS ( --Network fees (edit: also 1rune message events) paid to Reserve; no table for these specifically(?), so assuming by amount. --Summing and subtracting any same-size transfers already recorded in another table. SELECT DISTINCT block_timestamp, block_id, TO_CHAR('network_fees') AS asset, SUM(inflow) OVER(PARTITION BY block_timestamp, block_id) AS inflow FROM ( ( SELECT block_timestamp, block_id, 'network_fees' AS asset, POWER(10,-8) * amount_e8 AS inflow FROM thorchain.transfer_events WHERE (to_address = 'thor1dheycdevq39qlkxs2a6wuuzyn4aqxhve4qxtxt') AND (from_address <> 'thor1dheycdevq39qlkxs2a6wuuzyn4aqxhve4qxtxt') AND (amount_e8 IN (2000000, 1)) ) UNION ALL ( SELECT block_timestamp, block_id, asset, -1 * inflow AS inflow FROM union_without_network_fees HAVING inflow IN (0.00000001, 0.02) ) ) ), forgrouping AS ( --Combining intermediate tables into a single table, prior to the range-summing. (SELECT * FROM union_without_network_fees) UNION ALL (SELECT * FROM networkfeetable) ), filtered AS ( --Note that this is intended to keep the same blockgroup/block_timestamp/block_id final row values irrespective of blockchain filtering. SELECT block_timestamp, block_id, asset, --Careful; '_' doesn't seem to be handled reliably ('[_]' not fixing); avoid reference for now. IFF((asset NOT LIKE '%reserve%') AND (asset NOT LIKE '%rewards'), inflow, 0) AS net_income, IFF( (asset NOT LIKE '%reserve%') AND (asset NOT LIKE '%rewards') AND (asset NOT LIKE 'network%') AND (asset NOT LIKE '%ILP') AND (asset NOT LIKE '%upkeep') AND (asset <> 'thornames'), inflow, 0) AS outbound_fees_minus_gas, --(external income) IFF((asset LIKE '%ILP'), inflow, 0) AS impermanent_loss_protection_payouts, --(external outflow) IFF((asset LIKE '%upkeep'), inflow, 0) AS churn_costs, --(internal outflow) IFF((asset LIKE 'network%'), inflow, 0) AS network_fees, --Chain-unspecific network fees IFF((asset = 'thornames'), inflow, 0) AS thornames_income --Chain-unspecific THORName registration costs FROM forgrouping ) SELECT DISTINCT DATE(block_timestamp) AS date, SUM(net_income) OVER(ORDER BY date ASC) AS net_income_cumulative, SUM(outbound_fees_minus_gas) OVER(ORDER BY date ASC) AS outbound_fees_minus_gas_cumulative, SUM(network_fees) OVER(ORDER BY date ASC) AS network_fees_cumulative, SUM(churn_costs) OVER(ORDER BY date ASC) AS churn_costs_cumulative, SUM(impermanent_loss_protection_payouts) OVER(ORDER BY date ASC) AS impermanent_loss_protection_payouts_cumulative, SUM(thornames_income) OVER(ORDER BY date ASC) AS thornames_income_cumulative FROM filtered ORDER BY date DESC

2022-09-27 Cumulative Reserve income in RUNE ver16 (ignoring block rewards and slashes)