datavortexbnb
Updated 2024-11-23
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 SwapFees AS (
SELECT
t.tx_fee AS fee_in_bnb,
COUNT(s.tx_hash) AS number_of_swaps
FROM
bsc.core.fact_transactions t
JOIN
bsc.defi.ez_dex_swaps s
ON
t.tx_hash = s.tx_hash
WHERE
t.status = 'SUCCESS'
AND t.block_timestamp >= DATE_TRUNC('month', CURRENT_DATE)
AND t.block_timestamp < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
GROUP BY
t.tx_fee
),
BNBPrice AS (
SELECT
MEDIAN(price) AS bnb_price
FROM
bsc.price.ez_prices_hourly
WHERE
symbol = 'BNB'
AND hour >= DATE_TRUNC('month', CURRENT_DATE)
AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
)
SELECT
SUM(sf.fee_in_bnb) AS total_swap_fees_in_bnb,
SUM(sf.fee_in_bnb) * (SELECT bp.bnb_price FROM BNBPrice bp) AS total_swap_fees_in_usd,
SUM(sf.number_of_swaps) AS total_number_of_swaps,
ROUND(SUM(sf.fee_in_bnb) / SUM(sf.number_of_swaps), 4) AS avg_fee_per_swap_in_bnb,
ROUND((SUM(sf.fee_in_bnb) / SUM(sf.number_of_swaps)) * (SELECT bp.bnb_price FROM BNBPrice bp), 4) AS avg_fee_per_swap_in_usd
FROM
SwapFees sf;
QueryRunArchived: QueryRun has been archived