0xHaM-dDaily Liquidity Fees v. Block Rewards (FORKED FROM and CREDIT TO Polaris_9R) copy
Updated 2024-12-21
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
›
⌄
WITH fees AS (
SELECT
BLOCK_TIMESTAMP::DATE AS DATE,
SUM(LIQ_FEE_RUNE_USD) AS LIQUIDITY_FEES_USD
FROM thorchain.defi.fact_swaps
WHERE BLOCK_TIMESTAMP >= '{{start_date}}'::DATE
AND BLOCK_TIMESTAMP < '{{end_date}}'::DATE + INTERVAL '1 DAY'
GROUP BY 1
), rewards AS (
SELECT
BLOCK_TIMESTAMP::DATE AS DATE,
SUM(RUNE_AMOUNT_USD) AS BLOCK_REWARDS_USD
FROM thorchain.defi.fact_total_block_rewards
WHERE BLOCK_TIMESTAMP >= '{{start_date}}'::DATE
AND BLOCK_TIMESTAMP < '{{end_date}}'::DATE + INTERVAL '1 DAY'
GROUP BY 1
)
SELECT
COALESCE(f.DATE, r.DATE) AS DATE,
COALESCE(LIQUIDITY_FEES_USD, 0) AS LIQUIDITY_FEES_USD,
COALESCE(BLOCK_REWARDS_USD, 0) AS BLOCK_REWARDS_USD,
COALESCE(LIQUIDITY_FEES_USD, 0) + COALESCE(BLOCK_REWARDS_USD, 0) AS FEES_PLUS_REWARDS,
CASE
WHEN COALESCE(LIQUIDITY_FEES_USD, 0) + COALESCE(BLOCK_REWARDS_USD, 0) = 0 THEN 0
ELSE COALESCE(LIQUIDITY_FEES_USD, 0)/(COALESCE(LIQUIDITY_FEES_USD, 0) + COALESCE(BLOCK_REWARDS_USD, 0))
END * 100 AS PCT_LIQUIDTY_FEES,
0.5 AS HALF_LINE
FROM fees AS f
FULL OUTER JOIN rewards AS r
ON f.date = r.date
QueryRunArchived: QueryRun has been archived