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