feyikemiAptos DEX
Updated 2024-04-25
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
›
⌄
-- Credits to 0xHaM-d, his query on 'Top Projects Based on Transactions' helped put this query together
WITH Price as (
SELECT
hour :: date as p_date,
symbol,
avg(PRICE) as usd_price
FROM
aptos.price.ez_hourly_token_prices
WHERE
symbol = 'APT'
GROUP BY 1, 2
),
Fee AS (
SELECT
date(block_timestamp) AS Date,
Gas_used,
label_type,
INITCAP(Label) as DEXs,
tx_hash,
Sender,
((gas_used * gas_unit_price) / 1e8) as Gas_Fee,
FROM
aptos.core.fact_events a
JOIN aptos.core.dim_labels b on address = event_address
JOIN aptos.core.fact_transactions c using(tx_hash)
WHERE
b.label_type = 'dex'
AND SUCCESS = true
AND tx_type = 'user_transaction'
AND date > current_date - 30
),
Final AS (
SELECT
date,
QueryRunArchived: QueryRun has been archived