MLDZMNuser5
    Updated 2023-09-05
    with total as (select signers[0] as user, tx_id
    from solana.core.fact_events
    where succeeded = True
    and (program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
    or ARRAY_CONTAINS('PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'::VARIANT, instruction:accounts)) -- not all swaps done on Phoenix
    and BLOCK_TIMESTAMP>='2023-08-23'
    and signers[0] not in ('phxBcughCYKiYJxx9kYEkyqoAUL2RD3vyxSaL1gZRNG','Ba964S79RCU36fyVybAeeDLnrR1gwYQKo8qCpFiKJJAd',
    'JEEToN4w2cDyMJeFp7MA9kz2CLauChbsmo51kUDUxx9A','wowK1ivqmU9WhHnghiJQ4UUySndzvrUAJinjMUw54eX','GCRzo5mX7eZj4f4tNHLXB74HrDfzJhZpxTQ3dbbsKNsu',
    'LUKAzPV8dDbVykTVT14pCGKzFfNcgZgRbAXB8AGdKx3','D3VukrhsSXNM8f2wqx595cJaQtS22enTm9EgfumJT7Gv') -- signers for cancelling orders
    )

    select
    l.LABEL as project,
    count(distinct t.signers[0]) as sender,
    count(distinct t.tx_id) as no_txn
    from solana.core.fact_events t join solana.core.dim_labels l on t.PROGRAM_ID = l.address
    where t.block_timestamp>= '2023-08-01'
    and l.label_subtype != 'token_contract'
    and l.LABEL_TYPE in ('defi','nft','dex','dapp')
    and l.label != 'solana'
    and t.succeeded = TRUE
    and t.signers[0] in (select user from total)
    and t.tx_id not in (select tx_id from total)
    group by 1
    order by 2 desc limit 10
    Run a query to Download Data