Orion_9RRevenue vs Expenses
Updated 2024-02-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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,