SELECT

date_trunc('d',hour) as date,

avg(price) as price

FROM aptos.price.ez_prices_hourly

GROUP BY 1

,

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('{{granularity}}',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

FROM main

where date < current_date