piperSolana Mobile Seek - Chapter 2 Token Mints copy
    Updated 2024-10-22
    -- forked from marqu / Solana Mobile Seek - Chapter 2 Token Mints @ https://flipsidecrypto.xyz/marqu/q/hjig9FZnt3tk/solana-mobile-seek---chapter-2-token-mints

    with

    mint_txs as (

    select
    block_timestamp
    , tx_id
    , account_address
    , owner
    , instruction :parsed :info :tokenAmount :amount ::int as amount
    from solana.core.fact_events
    left join solana.core.fact_token_account_owners ta_owners
    on fact_events.instruction :parsed :info :account = ta_owners.account_address
    and fact_events.block_id >= ta_owners.start_block_id
    and iff(ta_owners.end_block_id is null, TRUE, fact_events.block_id <= ta_owners.end_block_id)
    where succeeded
    and program_id = 'TokenzQdBNbLqP5VEhdkAS6EPFLC1PHnBqCXEpPxuEb'
    and event_type = 'mintToChecked'
    and instruction :parsed :info :mint = '2DMMamkkxQ6zDMBtkFp8KH7FoWzBMBA1CGTYwom4QH6Z'
    and block_timestamp > '2024-02-02'
    ),

    aggregated as (

    select

    date_trunc('day', block_timestamp) as date
    , count(distinct owner) as buyers_daily
    , sum(amount) as units
    , sum(units) over (order by date) as units_cumul

    from mint_txs
    Last run: about 2 months ago
    DATE
    Buyers
    New Mints
    Cumulative Mints
    1
    2024-12-25 00:00:00.0002020155164
    2
    2024-12-24 00:00:00.0004040155144
    3
    2024-12-23 00:00:00.0003030155104
    4
    2024-12-22 00:00:00.0004041155074
    5
    2024-12-21 00:00:00.0005050155033
    6
    2024-12-20 00:00:00.0006060154983
    7
    2024-12-19 00:00:00.0008787154923
    8
    2024-12-18 00:00:00.000133134154836
    9
    2024-12-17 00:00:00.000160160154702
    10
    2024-12-16 00:00:00.00088101154542
    11
    2024-12-15 00:00:00.00098103154441
    12
    2024-12-14 00:00:00.0008083154338
    13
    2024-12-13 00:00:00.000119124154255
    14
    2024-12-12 00:00:00.000217224154131
    15
    2024-12-11 00:00:00.000663667153907
    16
    2024-12-10 00:00:00.000160164153240
    17
    2024-12-09 00:00:00.0004141153076
    18
    2024-12-08 00:00:00.0004144153035
    19
    2024-12-07 00:00:00.0005859152991
    20
    2024-12-06 00:00:00.0009191152932
    ...
    257
    10KB
    118s