datavortexpolygon
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
›
⌄
WITH SwapFees AS (
SELECT
t.tx_fee AS fee_in_matic,
COUNT(s.tx_hash) AS number_of_swaps
FROM
polygon.core.fact_transactions t
JOIN
polygon.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
),
MATICPrice AS (
SELECT
MEDIAN(price) AS matic_price
FROM
polygon.price.ez_prices_hourly
WHERE
symbol = 'MATIC'
AND hour >= DATE_TRUNC('month', CURRENT_DATE)
AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
)
SELECT
SUM(sf.fee_in_matic) AS total_swap_fees_in_matic,
SUM(sf.fee_in_matic) * (SELECT mp.matic_price FROM MATICPrice mp) AS total_swap_fees_in_usd,
SUM(sf.number_of_swaps) AS total_number_of_swaps,
ROUND(SUM(sf.fee_in_matic) / SUM(sf.number_of_swaps), 4) AS avg_fee_per_swap_in_matic,
ROUND((SUM(sf.fee_in_matic) / SUM(sf.number_of_swaps)) * (SELECT mp.matic_price FROM MATICPrice mp), 4) AS avg_fee_per_swap_in_usd
FROM
SwapFees sf;
QueryRunArchived: QueryRun has been archived