MLDZMNmon3
    Updated 2024-02-05
    with t1 as (select
    tx_id
    from solana.core.fact_transactions
    where instructions[0]:programId = 'monacoUXKtUi6vKsQwaLyxmXKSievfNWEcYXTgkbCih'
    and block_timestamp > current_date-{{Days_back}} --'2022-11-22'
    and split(log_messages[1], ':')[2] like '%CreateOrder%'
    ),

    price_tab as (SELECT
    RECORDED_HOUR::date as day,
    TOKEN_ADDRESS,
    Symbol,
    avg(CLOSE) as price

    from solana.price.ez_token_prices_hourly
    group by 1,2,3
    )

    select
    date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
    Upper(p.symbol) as token,
    count(distinct tx_id) as no_orders,
    count(distinct TX_FROM) as no_users,
    sum(AMOUNT*price) as volume_usd,
    avg(AMOUNT*price) as avg_volume

    from solana.core.fact_transfers s
    left join price_tab p on s.block_timestamp::date=p.day and s.mint=p.TOKEN_ADDRESS
    where tx_id in (select tx_id from t1)
    and block_timestamp > current_date-{{Days_back}}
    group by 1,2 having token is not null
    Last run: about 1 year ago
    DATE
    TOKEN
    NO_ORDERS
    NO_USERS
    VOLUME_USD
    AVG_VOLUME
    1
    2024-01-23 00:00:00.000USDC437864516522.79678075542.590436903
    2
    2024-01-14 00:00:00.000USDC13192542785077.70627391368.290565937
    3
    2024-01-25 00:00:00.000USDC286343243931.67696113522.120018825
    4
    2024-01-07 00:00:00.000USDC666256120027.98396654399.479633418
    5
    2024-01-13 00:00:00.000USDC1244649157056.5393178308.921683399
    6
    2024-02-02 00:00:00.000USDC611673160796.57213173235.897945528
    7
    2024-01-24 00:00:00.000USDC301855482446.12514011891.889722652
    8
    2024-01-29 00:00:00.000USDC615143359583.57596257279.825385304
    9
    2024-01-20 00:00:00.000USDC8064914923802.8258129857.098715013
    10
    2024-01-15 00:00:00.000USDC10448636131105.79497531394.000763724
    11
    2024-02-03 00:00:00.000USDC776653031500.70629555197.67219003
    12
    2024-01-11 00:00:00.000USDC312364047559.89616626591.316274093
    13
    2024-01-08 00:00:00.000USDC13689515635751.4891677497.557724397
    14
    2024-01-28 00:00:00.000USDC325772539153.23651332395.876712896
    15
    2024-01-10 00:00:00.000USDC10754429246422.19521471147.818767473
    16
    2024-01-22 00:00:00.000USDC456864423963.82483017498.362490124
    17
    2024-01-21 00:00:00.000USDC551266761432.73311163659.58762395
    18
    2024-01-27 00:00:00.000USDC377152277423.97204831300.967883183
    19
    2024-01-17 00:00:00.000USDC5430611614439.8306429926.856582128
    20
    2024-01-18 00:00:00.000USDC5365511581664.350908930.627910881
    31
    2KB
    502s