flipside

    claimers txs
    Blazeclaimers txs
    Updated 2024-02-12
    WITH airdrops AS (
    SELECT DISTINCT
    block_timestamp,
    tx_id
    FROM solana.core.fact_events
    WHERE succeeded
    AND program_id = 'EXxqB6XPLczReFcZyigfbdowB6WGYtnkLYC4XZ2ae9ch'
    ),
    claimers AS (
    SELECT
    tx_to as wallet_address,
    sum(amount) AS total_pyth
    FROM solana.core.fact_transfers
    JOIN airdrops USING (block_timestamp, tx_id)
    WHERE lower(mint) = lower('HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3')
    GROUP BY 1
    )

    SELECT
    date_trunc('day', ft.block_timestamp) AS date,
    COUNT(ft.tx_id) as num_txs
    FROM
    solana.core.fact_transactions AS ft
    where
    (ft.signers[0] in (select wallet_address from claimers)
    or ft.signers[1] in (select wallet_address from claimers)
    or ft.signers[2] in (select wallet_address from claimers)
    )
    and date_trunc('month', ft.block_timestamp) > date_trunc('month', current_timestamp) - INTERVAL '6 months'
    group by date
    order by date
    Last run: about 1 year ago
    DATE
    NUM_TXS
    1
    		2023-09-01 00:00:00.0002916494
    2
    		2023-09-02 00:00:00.0002371147
    3
    		2023-09-03 00:00:00.0002303929
    4
    		2023-09-04 00:00:00.0002540221
    5
    		2023-09-05 00:00:00.0003132852
    6
    		2023-09-06 00:00:00.0002887690
    7
    		2023-09-07 00:00:00.0002679114
    8
    		2023-09-08 00:00:00.0002472014
    9
    		2023-09-09 00:00:00.0002081842
    10
    		2023-09-10 00:00:00.0003126983
    11
    		2023-09-11 00:00:00.0003367865
    12
    		2023-09-12 00:00:00.0003211703
    13
    		2023-09-13 00:00:00.0003163402
    14
    		2023-09-14 00:00:00.0002974894
    15
    		2023-09-15 00:00:00.0002665431
    16
    		2023-09-16 00:00:00.0002726742
    17
    		2023-09-17 00:00:00.0002436048
    18
    		2023-09-18 00:00:00.0002985565
    19
    		2023-09-19 00:00:00.0002650883
    20
    		2023-09-20 00:00:00.0002651575
    ...
    165
    6KB
    516s