MLDZMNuser5
Updated 2023-09-05
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
›
⌄
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