CartanGroupAptos overview over time
    Updated 2023-12-07
    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