marquDrift v2 - txs and wallets
    Updated 2024-09-12
    with

    drift_users as (

    select

    block_timestamp,
    tx_id,
    signers[0] as wallet_address

    from solana.core.fact_events events
    inner join solana.core.fact_transactions txs
    using(tx_id,block_timestamp,succeeded)
    where succeeded
    and program_id = 'dRiftyHA39MWEi3m9aunc5MzRF1JYuBsbn6VPcn33UH'
    and block_timestamp > '2022-11-04' ::date
    and block_timestamp ::date > current_date() - interval '{{months}} months' - interval '1 month'
    ),

    aggregated as (

    select

    date_trunc('day',block_timestamp) as date,
    count(distinct tx_id) as txs,
    avg(txs) over (order by date rows between 6 preceding and current row) as avg_7d_txs,
    avg(txs) over (order by date rows between 13 preceding and current row) as avg_14d_txs,
    avg(txs) over (order by date rows between 29 preceding and current row) as avg_30d_txs,
    count(distinct wallet_address) as wallets,
    avg(wallets) over (order by date rows between 6 preceding and current row) as avg_7d_w,
    avg(wallets) over (order by date rows between 13 preceding and current row) as avg_14d_w,
    avg(wallets) over (order by date rows between 29 preceding and current row) as avg_30d_w

    from drift_users
    Last run: 2 months ago
    DATE
    TXS
    AVG_7D_TXS
    AVG_14D_TXS
    AVG_30D_TXS
    WALLETS
    AVG_7D_W
    AVG_14D_W
    AVG_30D_W
    1
    2024-11-20 00:00:00.000606577670573.857692723.285776390.13333633207.4283547.2853141.566
    2
    2024-11-21 00:00:00.000582402652426690438.142769406.83338153289.2853611.2143201.333
    3
    2024-11-22 00:00:00.000635784646920.857687957.714763897.73335113365.57135943215.233
    4
    2024-11-23 00:00:00.000628160642329685230.214759739.833338834593538.7853225
    5
    2024-11-24 00:00:00.000621410626835.714673705.285750736.635653468.4283494.5713256.4
    6
    2024-11-25 00:00:00.000660518629008.142673446.714739977.53338313519.5713471.7853315
    7
    2024-11-26 00:00:00.000706207634436.857665533.5731515.238063611.2853458.7143371.4
    8
    2024-11-27 00:00:00.000562501628140.285649357.071717821.33331923586.8573397.1423396.466
    9
    2024-11-28 00:00:00.000592019629514.142640970.071705757.43328273445.7143367.53293.066
    10
    2024-11-29 00:00:00.000578634621349.857634135.357691822.96631433393.1423379.3573289.9
    11
    2024-11-30 00:00:00.000592577616266.571629297.785676344.3255432743366.53292.7
    12
    2024-12-01 00:00:00.000561963607774.142617304.92865856027713160.5713314.53280.266
    13
    2024-12-02 00:00:00.000765512622773.285625890.714656289.33338243159.5713339.5713342.166
    14
    2024-12-03 00:00:00.000665948617022625729.42865887036643139.2853375.2853395.033
    15
    2024-12-04 00:00:00.000734089641534.571634837.428662999.76636783208.7143397.7853447.9
    16
    2024-12-05 00:00:00.000832712675919.285652716.714670200.56645273451.5713448.6423521.633
    17
    2024-12-06 00:00:00.000700467693324657336.928670634.341793599.5713496.3573531.233
    18
    2024-12-07 00:00:00.000685671706623.142661444.857673010.233023706.4283490.2143543.966
    19
    2024-12-08 00:00:00.000642222718088.714662931.428672067.26626973695.8573428.2143508.8
    20
    2024-12-09 00:00:00.000907567738382.285680577.78568010842203752.42834563510.766
    30
    3KB
    345s