messariBSC top contracts (by users)
Updated 2023-07-13
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
›
⌄
WITH DailyUniqueUserCounts AS (
SELECT
TO_ADDRESS,
COUNT(DISTINCT FROM_ADDRESS) AS unique_user_count,
DATE(BLOCK_TIMESTAMP) AS date
FROM
bsc.core.fact_transactions
GROUP BY
TO_ADDRESS,
DATE(BLOCK_TIMESTAMP)
),
RankedDailyUniqueUserCounts AS (
SELECT
TO_ADDRESS,
unique_user_count,
date,
RANK() OVER (PARTITION BY date ORDER BY unique_user_count DESC) AS unique_user_rank,
SUM(unique_user_count) OVER (PARTITION BY date) AS total_daily_unique_users
FROM
DailyUniqueUserCounts
)
SELECT
date,
TO_ADDRESS,
unique_user_count,
unique_user_rank,
unique_user_count * 100.0 / total_daily_unique_users AS unique_user_percentage
FROM
RankedDailyUniqueUserCounts
WHERE
unique_user_rank <= 5
ORDER BY
date desc,
unique_user_rank asc;
Run a query to Download Data