HadisehTop
Updated 2024-10-29
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
›
⌄
-- Calculate base transaction statistics
WITH transaction_metrics AS (
SELECT
COUNT(DISTINCT TX_HASH) / NULLIF(COUNT(DISTINCT FROM_ADDRESS), 0) AS avg_transactions_per_user,
COUNT(TX_HASH) AS total_transaction_count,
COUNT(DISTINCT FROM_ADDRESS) AS new_user_count
FROM
base.core.fact_transactions AS ft
WHERE
BLOCK_TIMESTAMP >= '2024-01-01'
AND BLOCK_TIMESTAMP = (
SELECT MIN(BLOCK_TIMESTAMP)
FROM base.core.fact_transactions
WHERE FROM_ADDRESS = ft.FROM_ADDRESS
)
),
-- Calculate platform-level transaction counts and unique user counts
platform_stats AS (
SELECT
platform,
COUNT(*) AS transaction_count,
COUNT(DISTINCT origin_from_address) AS unique_users
FROM base.defi.ez_dex_swaps
WHERE block_timestamp >= '2024-01-01'
GROUP BY platform
)
-- Select the top 10 platforms by transaction count, including unique user count
SELECT
platform,
transaction_count,
unique_users
FROM platform_stats
ORDER BY transaction_count DESC
LIMIT 10;
QueryRunArchived: QueryRun has been archived