datavortextransaction fees
Updated 2024-11-27
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
›
⌄
⌄
--SELECT DISTINCT SYMBOL, price FROM kaia.price.ez_prices_hourly
--WHERE SYMBOL ILIKE 'KLAY'
WITH avg_klay_price AS (
SELECT
DATE_TRUNC('day', hour) AS price_date,
AVG(price) AS avg_price_klay
FROM
kaia.price.ez_prices_hourly
WHERE
symbol = 'KLAY'
GROUP BY
DATE_TRUNC('day', hour)
),
fees_with_prices AS (
SELECT
s.tx_hash,
ft.tx_fee,
akp.avg_price_klay
FROM
kaia.defi.ez_dex_swaps s
JOIN kaia.core.fact_transactions ft ON s.tx_hash = ft.tx_hash
LEFT JOIN avg_klay_price akp ON DATE_TRUNC('day', s.block_timestamp) = akp.price_date
WHERE
s.block_timestamp > CURRENT_TIMESTAMP - INTERVAL '30 days'
)
SELECT
SUM(fwp.tx_fee * fwp.avg_price_klay) AS total_tx_fee_usd,
SUM(fwp.tx_fee * fwp.avg_price_klay) * 1.0 / COUNT(DISTINCT fwp.tx_hash) AS average_fee_per_swap_usd
FROM
fees_with_prices fwp;
/*
SELECT
SUM(ft.tx_fee) AS total_tx_fee,
QueryRunArchived: QueryRun has been archived