BlockTrackervolume by new users (last month)
    Updated 2024-10-15
    with volume_last_months as (
    select
    'total' as type,
    sum(event_data:size_delta) / pow(10, 6) as amount
    from aptos.core.fact_events
    where event_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'
    and event_module = 'trading'
    and event_resource = 'PositionEvent'
    and account_address = event_data : user
    and block_timestamp::date >= current_date - 30
    and SUCCESS = 'TRUE'
    )
    ,
    new as (
    select user
    from (
    SELECT
    event_data : user as user,
    min(block_timestamp) as first_tx
    from aptos.core.fact_events
    where event_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'
    and event_module = 'trading'
    and event_resource = 'PositionEvent'
    and account_address = event_data : user
    and block_timestamp::date >= '2023-08-01'
    and SUCCESS = 'TRUE'
    GROUP BY 1)
    where first_tx::date >= current_date - 30
    )
    ,
    volume_by_new as (
    select
    date_trunc('day', block_timestamp) as date,
    'new' as type,
    sum(event_data:size_delta) / pow(10, 6) as amount
    from aptos.core.fact_events
    QueryRunArchived: QueryRun has been archived