CartanGroupAptos overview over time
Updated 2023-12-07
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 TokenPrices AS (
SELECT
TO_TIMESTAMP(value[0]::string) as date,
value[1] as price
FROM (
SELECT livequery.live.udf_api(
'https://api.coingecko.com/api/v3/coins/aptos/market_chart?vs_currency=usd&days=max&interval=daily&precision=3') as response
),LATERAL FLATTEN (input => response:data:prices)
)
,
main as (
SELECT
block_timestamp::date as dates,
success,
tx_hash,
block_number,
gas_used,
gas_unit_price,
((gas_used * gas_unit_price)/1e8) * b.price as tx_fee_usd
FROM aptos.core.fact_transactions a
LEFT JOIN TokenPrices b ON date_trunc('d', block_timestamp) = b.date
)
SELECT
date_trunc('day',dates) as date,
count(DISTINCT tx_hash) as n_txs,
sum(CASE WHEN success = 'true' THEN 1 ELSE 0 END) as successful_txns,
sum(CASE WHEN success != 'true' THEN 1 ELSE 0 END) as unsuccessful_txns,
100*successful_txns/(successful_txns+unsuccessful_txns) as success_rate,
sum(n_txs) over (ORDER BY date) as cum_txs,
count(DISTINCT block_number) as block_number_count,
sum(block_number_count) over (ORDER BY date) as cum_block,
sum((gas_used * gas_unit_price)/1e8) as "Gas Fee [APT]",
sum("Gas Fee [APT]") over (ORDER by date) as cum_fee_apt,
sum(tx_fee_usd) as "Gas Fee [USD]",
sum("Gas Fee [USD]") over (ORDER by date) as cum_fee_usd
Run a query to Download Data