hessTotal amount per project III copy II
Updated 2024-03-25
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
26
27
28
29
30
31
32
33
›
⌄
with wallet as ( select trunc(block_timestamp,'day') as date, tx_receiver , deposit/pow(10,24) as near_amount
from near.core.fact_Transfers
where (tx_signer ilike '%sweat_welcome.near%' or tx_signer ilike '%sweat_oracle_%' ) and status = 'TRUE'
and tx_receiver not in ('sweat_welcome.near'))
,
sweat_wallet as ( select DISTINCT tx_signer
from near.core.fact_transfers a join near.core.dim_address_labels b on a.tx_receiver = b.address
where project_name ilike '%sweat%'
)
,
not_sweat as ( select DISTINCT tx_signer
from near.core.fact_transfers a join near.core.dim_address_labels b on a.tx_receiver = b.address
where project_name not ilike '%sweat%'
and tx_signer in (select tx_signer from sweat_wallet))
select project_name, count(DISTINCT(tx_signer)) as total_users,
count(DISTINCT(tx_hash)) as total_tx, sum(deposit/pow(10,24)) as near_amount
from near.core.fact_transfers a join near.core.dim_address_labels b on a.tx_receiver = b.address
where block_timestamp::date >= '2023-01-01'
and tx_signer in (select tx_signer from not_sweat)
and label_type not in ('chadmin','token')
and project_name not ilike '%sweat%'
and label_type != 'cex'
and project_name not ilike '%usdc%'
and project_name not ilike '%tether%'
group by 1
order by 2 desc
limit 20
QueryRunArchived: QueryRun has been archived