Orion_9RRevenue vs Expenses
    Updated 2024-02-13
    -- NETWORK FEES
    WITH network_fees_table AS (SELECT distinct to_date(block_timestamp) as date, 0.02 * COUNT(dim_block_id) as network_fee
    FROM thorchain.core.fact_transfer_events
    WHERE to_address = 'thor1dheycdevq39qlkxs2a6wuuzyn4aqxhve4qxtxt' AND amount_e8 = 2000000 GROUP BY date),
    -- SLASHING INCOME
    slashing_income_table AS (SELECT DISTINCT to_date(block_timestamp) as date, SUM(amount_e8) / POW(10,8) as slashing_income
    FROM thorchain.core.fact_transfer_events
    WHERE from_address = 'thor17gw75axcnr8747pkanye45pnrwk7p9c3cqncsv' AND to_address = 'thor1dheycdevq39qlkxs2a6wuuzyn4aqxhve4qxtxt' GROUP BY date),
    -- OUTBOUND FEE
    outbound_fee_table AS (SELECT distinct to_date(block_timestamp) as date, SUM(pool_deduct) / POW(10,8) as outbound_fee
    FROM thorchain.defi.fact_fee_events GROUP BY date),
    -- GAS REIMBRUSEMENT
    gas_reimbursement_table AS (SELECT distinct to_date(block_timestamp) as date, (SUM(rune_e8) / POW(10,8)) as gas_reimbursement
    FROM thorchain.defi.fact_gas_events GROUP BY date),
    -- IL PROTECTION
    il_protection_table AS (SELECT day as date, SUM(IL_PROTECTION_PAID) as il_protection
    FROM thorchain.defi.fact_daily_pool_stats GROUP BY date),
    -- BLOCK REWARDS
    block_reward_table AS (SELECT day as date, block_rewards FROM thorchain.defi.fact_block_rewards),
    -- LIQ FEES
    liquidity_fees_table AS (SELECT day as date, liquidity_fees FROM thorchain.defi.fact_daily_earnings),

    joined_table AS (SELECT a.date, coalesce(network_fee, 0) as network_fee,
    coalesce(slashing_income, 0) as slashing_income,
    coalesce(outbound_fee, 0) as outbound_fee, coalesce(gas_reimbursement, 0) as gas_reimbursement,
    coalesce(il_protection, 0) as il_protection, coalesce(block_rewards, 0) as block_rewards,
    liquidity_fees
    FROM network_fees_table as a
    LEFT JOIN slashing_income_table as b ON a.date = b.date
    LEFT JOIN outbound_fee_table as c ON a.date = c.date
    LEFT JOIN gas_reimbursement_table as d ON a.date = d.date
    LEFT JOIN il_protection_table as e ON a.date = e.date
    LEFT JOIN block_reward_table as f ON a.date = f.date
    LEFT JOIN liquidity_fees_table as g ON a.date = g.date)

    SELECT date as day, network_fee, slashing_income, liquidity_fees, outbound_fee,