zackmendel4a. Weekly Top 10 Dapps Used CEX Users copy
Updated 2023-05-02
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
34
35
36
›
⌄
-- forked from 4a. Top 10 Dapps Used CEX Users @ https://flipsidecrypto.xyz/edit/queries/05e9a5cc-7685-4a64-b71d-29ee1d175a75
WITH cex_users AS (
SELECT
tx_receiver AS wallets
FROM near.core.fact_transfers t JOIN near.core.dim_address_labels l
ON t.tx_signer = l.address
WHERE label_type = 'cex'
AND status = 'true'
UNION
SELECT
tx_signer AS wallets
FROM near.core.fact_transfers t JOIN near.core.dim_address_labels l
ON t.tx_receiver = l.address
WHERE label_type = 'cex'
AND status = 'true'
),
top_dapps AS (
SELECT
DISTINCT project_name,
COUNT (DISTINCT signer_id) AS user
FROM near.core.fact_actions_events e JOIN near.core.dim_address_labels l
ON e.receiver_id = l.address
WHERE l.label_type = 'dapp'
AND signer_id IN (SELECT wallets FROM cex_users)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
)
SELECT
DISTINCT project_name,
date(date_trunc(week, block_timestamp)) AS timespan,
COUNT (DISTINCT signer_id) AS user,
sum (user) over (partition BY project_name ORDER BY timespan) AS cumul_user
Run a query to Download Data