NakedCollectorgrand-chocolate
Updated 2024-08-19
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
›
⌄
WITH tokens AS (
SELECT contract_address, symbol FROM (
SELECT '0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599' AS contract_address, 'WBTC' AS symbol
UNION ALL SELECT '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2', 'WETH'
-- ... [other token selections remain unchanged]
) t
),
token_activity_current AS (
SELECT
t.CONTRACT_ADDRESS,
t.SYMBOL,
COUNT(DISTINCT tx_hash) AS transaction_count,
COUNT(DISTINCT FROM_ADDRESS) AS unique_users,
SUM(AMOUNT_USD) AS total_volume
FROM
ethereum.core.ez_token_transfers e
JOIN tokens t ON e.CONTRACT_ADDRESS = t.CONTRACT_ADDRESS
WHERE
block_timestamp >= DATEADD(month, -1, CURRENT_DATE())
GROUP BY
t.CONTRACT_ADDRESS,
t.SYMBOL
),
token_activity_previous AS (
SELECT
t.CONTRACT_ADDRESS,
COUNT(DISTINCT tx_hash) AS transaction_count,
COUNT(DISTINCT FROM_ADDRESS) AS unique_users
FROM
ethereum.core.ez_token_transfers e
JOIN tokens t ON e.CONTRACT_ADDRESS = t.CONTRACT_ADDRESS
WHERE
block_timestamp BETWEEN DATEADD(month, -2, CURRENT_DATE()) AND DATEADD(month, -1, CURRENT_DATE())
GROUP BY
t.CONTRACT_ADDRESS
),
QueryRunArchived: QueryRun has been archived