HitmonleeCryptoAll Fee Calculator
Updated 2024-10-24
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
-- Monthly fee volume with Jupiter swaps
monthly_jupiter_fees AS (
SELECT
DATE_TRUNC('month', tx.block_timestamp) AS month,
SUM(tx.fee / 1000000000 * price.price) AS monthly_fee_total_usd
FROM
solana.core.fact_transactions tx
JOIN
solana.defi.ez_dex_swaps s ON tx.tx_id = s.tx_id
JOIN
solana.price.ez_prices_hourly price ON DATE_TRUNC('hour', tx.block_timestamp) = price.hour
WHERE
-- s.swap_program LIKE 'jupiter%'
s.swapper = '{{Address}}'
AND price.token_address = 'So11111111111111111111111111111111111111112'
GROUP BY
month
)
SELECT
f.month,
COALESCE(f.monthly_fee_total_usd, 0) AS monthly_fee_total_usd,
SUM(COALESCE(f.monthly_fee_total_usd, 0)) OVER (ORDER BY f.month) AS running_total_fees_usd,
SUM(CASE WHEN DATE_PART('year', f.month) = 2024 THEN f.monthly_fee_total_usd ELSE 0 END) OVER () AS total_fees_2024_usd,
SUM(CASE WHEN DATE_PART('year', f.month) < 2024 THEN f.monthly_fee_total_usd ELSE 0 END) OVER () AS total_fees_before_2024_usd,
CASE WHEN DATE_PART('year', f.month) = 2024 THEN
100.0 * SUM(CASE WHEN DATE_PART('year', f.month) = 2024 THEN f.monthly_fee_total_usd ELSE 0 END) OVER () /
NULLIF(SUM(f.monthly_fee_total_usd) OVER (), 0)
ELSE 0 END AS percent_fees_2024,
CASE WHEN DATE_PART('year', f.month) < 2024 THEN
100.0 * SUM(CASE WHEN DATE_PART('year', f.month) < 2024 THEN f.monthly_fee_total_usd ELSE 0 END) OVER () /
NULLIF(SUM(f.monthly_fee_total_usd) OVER (), 0)
ELSE 0 END AS percent_fees_before_2024
FROM
monthly_jupiter_fees f
QueryRunArchived: QueryRun has been archived