adriaparcerisasNode Operator vs Liquidity Provider Yield 2
Updated 2024-12-19
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
›
⌄
WITH rewards AS (
SELECT
day AS date,
bonding_earnings,
liquidity_earnings
FROM thorchain.defi.fact_block_rewards
ORDER BY 1 ASC
),
lps AS (
SELECT
TRUNC(block_timestamp, 'week') AS date,
count(distinct from_address) as active_lps
FROM thorchain.defi.fact_liquidity_actions
GROUP BY 1
ORDER BY 1 ASC
),
nodes AS (
SELECT
TRUNC(block_timestamp, 'week') AS date,
COUNT(DISTINCT node_address) AS n_active_nodes
FROM thorchain.defi.fact_update_node_account_status_events
WHERE current_status = 'Active'
GROUP BY 1
ORDER BY 1 ASC
)
SELECT
x.date,
bonding_earnings,
n_active_nodes,
bonding_earnings / n_active_nodes AS bonding_earnings_per_node,
liquidity_earnings,
liquidity_earnings / active_lps AS LP_earnings_per_user,
FROM rewards x
JOIN lps y ON x.date = y.date
JOIN nodes z ON x.date = z.date
where x.date<trunc(current_date,'week')
QueryRunArchived: QueryRun has been archived