Abbas_ra21Volume daily
    Updated 2024-12-14
    with main AS (select
    block_timestamp,
    TX_HASH,
    version,
    case
    when t1.EVENT_DATA:is_sell = false then 'buy'
    else 'sell'
    end AS Action,
    t1.EVENT_DATA:swapper AS swapper,
    case when t1.EVENT_DATA:is_sell = false then t1.EVENT_DATA:input_amount / 1e8
    else t1.EVENT_DATA:quote_volume / 1e8 end AS APT_Size,
    Apt_Size*price AS USD_size,
    t2.EVENT_DATA:market_metadata:market_address AS market_address,
    t1.EVENT_DATA:market_id AS market_id,
    t2.event_data:instantaneous_stats:market_cap / 1e8 AS Mcap,
    mcap*price AS Mcap_USD,
    t2.event_data:instantaneous_stats:fully_diluted_value / 1e8 AS fdv,
    fdv*price AS fdv_USD,
    t2.event_data:instantaneous_stats:total_value_locked / 1e8 AS TVL,
    TVL*price AS TVL_USD,
    from
    aptos.core.fact_events t1
    inner join aptos.core.fact_events t2 using(TX_HASH, version)
    inner join aptos.price.ez_prices_hourly on hour=date_trunc('hour',t1.block_timestamp) and TOKEN_ADDRESS='0x1::aptos_coin::AptosCoin'
    where
    t2.EVENT_RESOURCE = 'State'
    and t1.EVENT_RESOURCE = 'Swap'
    and t1.event_module = 'emojicoin_dot_fun')

    select
    block_timestamp::Date AS day,
    Action,
    sum(APT_Size) AS "Volume (APT)",
    sum(USD_size) AS "Volume (USD)",
    count(*) AS TX,
    count(distinct swapper ) AS swappers
    Last run: 25 days ago
    DAY
    ACTION
    Volume (APT)
    Volume (USD)
    TX
    SWAPPERS
    1
    2024-11-27 00:00:00.000sell8149.4435358998172.269665809565273
    2
    2024-11-22 00:00:00.000sell51059.36371184614297.58978068337691347
    3
    2024-11-30 00:00:00.000buy1614.9318064921455.4757696434816245
    4
    2024-11-26 00:00:00.000buy3740.5952778444174.97237914963701501
    5
    2024-12-04 00:00:00.000sell9752.69565649140633.761575906634197
    6
    2024-11-20 00:00:00.000sell891550.5707063710835802.0734052404839425
    7
    2024-11-25 00:00:00.000buy7982.5096396699138.5977689136760304
    8
    2024-12-01 00:00:00.000buy3479.0549023246668.5315617417734126
    9
    2024-12-07 00:00:00.000buy2894.6077069743009.7601776987020347
    10
    2024-11-23 00:00:00.000sell12774.90095169161892.9753683651318478
    11
    2024-11-20 00:00:00.000buy1015724.7935027612280247.0964189143215939
    12
    2024-11-27 00:00:00.000buy7576.1077034991455.6662461232838318
    13
    2024-12-13 00:00:00.000sell1807.0564039525325.303851675600307
    14
    2024-12-05 00:00:00.000buy20948.92625526293938.4508675452659400
    15
    2024-12-06 00:00:00.000buy8329.14132881118711.6847000198972399
    16
    2024-12-03 00:00:00.000sell3724.5797346552319.309451434390126
    17
    2024-12-12 00:00:00.000sell3604.7494905449469.231816072584330
    18
    2024-11-24 00:00:00.000buy10135.9244315128609.98412969512903561
    19
    2024-12-11 00:00:00.000sell2157.4802154326284.139626026612306
    20
    2024-11-26 00:00:00.000sell4019.8483787146746.64444149518251537
    50
    4KB
    20s