feyikemiTop Projects Based on Transactions copy copy
Updated 2024-04-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
›
⌄
-- forked from 0xHaM-d / Top Projects Based on Transactions copy @ https://flipsidecrypto.xyz/0xHaM-d/q/VulZkkXbpdLd/top-projects-based-on-transactions-copy
-- forked from hess / Top Projects Based on Transactions @ https://flipsidecrypto.xyz/hess/q/VXeeS5RxHEki/top-projects-based-on-transactions
with priceTb 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(c.block_timestamp) as date,
gas_used,
INITCAP(LABEL_TYPE) as Type,
iff(INITCAP(LABEL)='Move Dollar','Thala',INITCAP(LABEL)) as LABEL,
LABEL_SUBTYPE,
address_name,
c.block_number,
c.tx_hash,
sender as tx_from,
((gas_used * gas_unit_price)/1e8) as amount
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 SUCCESS=true
and tx_type = 'user_transaction'
AND c.block_timestamp::date >= '2024-01-01'
and c.block_timestamp::date between (current_date - 31) and (current_date - 1)
and c.block_timestamp::date between (current_date - 31) and (current_date - 1)
)
,
QueryRunArchived: QueryRun has been archived