datavikingMoonshot daily activity
    Updated 2 days ago
    with start_date as (
    --select dateadd(day, -24, current_timestamp()) as start_date
    select '2024-07-18' as start_date
    )
    , moonshot_ca as (
    select '7rtiKSUDLBm59b1SBmD9oajcP8xE64vAGSMbAN5CXy1q' as ca
    , '5wkyL2FLEcyUUgc3UeGntHTAfWfzDrVuxMnaMm7792Gk' as fee_wallet
    )
    , txs as (
    select s.* ,
    t.signers[1] as user_wallet
    from solana.defi.ez_dex_swaps s
    left join solana.core.fact_transactions t on s.tx_id = t.tx_id
    where s.swapper in (select ca from moonshot_ca)
    and s.block_timestamp >= (select start_date from start_date)
    and t.block_timestamp >= (select start_date from start_date)
    )

    , trades as (
    select
    user_wallet,
    DATE_TRUNC('day', block_timestamp) AS tx_day,
    swap_from_mint as mint,
    swap_from_symbol as symbol,
    -(swap_from_amount) as amount,
    -(swap_from_amount_usd) as amount_usd,
    null as buy_amt,
    null as buy_amt_usd,
    swap_from_amount as sell_amt,
    swap_from_amount_usd as sell_amt_usd,
    'sell' as type
    from txs
    where swap_from_mint not in (
    'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' --(not usdc)
    , 'So11111111111111111111111111111111111111112' -- (not wsol)
    , 'So11111111111111111111111111111111111111111' -- (not sol)
    Last run: 2 months ago
    TX_DAY
    TXS
    BUY_TXS
    SELL_TXS
    USERS
    TXS_PER_USER
    BUY_AMT_USD
    SELL_AMT_USD
    NET_VOLUME_USD
    1
    2024-12-04 00:00:00.00054333243219021592.516443311921.36260329.4451591.92
    2
    2024-12-03 00:00:00.000161358886724948343.3378151144410.641064392.3980018.25
    3
    2024-12-02 00:00:00.000168568867798948043.5087431222604.161253335.11-30730.95
    4
    2024-12-01 00:00:00.0001969111432825953413.6867631031884.931088606.46-56721.530000001
    5
    2024-11-30 00:00:00.0002321213437977557154.0615921455977.141338735.97117241.17
    6
    2024-11-29 00:00:00.0002026811834843457373.5328571178164.271046018.72132145.55
    7
    2024-11-28 00:00:00.00030101181191198275883.9669211993352.091720450.98272901.11
    8
    2024-11-27 00:00:00.00034674204081426687153.9786572760903.882542281.79218622.09
    9
    2024-11-26 00:00:00.00029341163351300673753.9784411568719.081946713.59-377994.51
    10
    2024-11-25 00:00:00.00035529204881504185454.157872409286.042335726.3373559.709999999
    11
    2024-11-24 00:00:00.00030649172641338577733.9430081666725.831687370.45-20644.619999999
    12
    2024-11-23 00:00:00.00031088176181347077594.0067021703435.041894932.26-191497.22
    13
    2024-11-22 00:00:00.000472022747719725104814.5035782764457.383183523.86-419066.48
    14
    2024-11-21 00:00:00.000646503692927721121655.3144274180771.364399387.9-218616.540000001
    15
    2024-11-20 00:00:00.000965815703939542138816.9577846477946.617261500.71-783554.100000003
    16
    2024-11-19 00:00:00.00035222205481467471884.9001113013141.663217003.63-203861.969999999
    17
    2024-11-18 00:00:00.00030656183561230063454.8315212793016.362880163.15-87146.790000001
    18
    2024-11-17 00:00:00.0002313713817932051584.4856531509775.81552001.08-42225.28
    19
    2024-11-16 00:00:00.0002415614423973353034.5551571940484.081972717.5-32233.42
    20
    2024-11-15 00:00:00.0002183513274856150394.3332012353019.712144714.94208304.77
    ...
    140
    11KB
    573s