NakedCollectorSNX Case Study
Updated 2024-08-12
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
›
⌄
-- forked from Aave Case Study @ https://flipsidecrypto.xyz/studio/queries/d7e4c854-1b66-40a5-ba7b-cace44ed8e62
SELECT
DATE_TRUNC('week', t.BLOCK_TIMESTAMP) AS Date,
t.symbol,
AVG(t.AMOUNT_USD) AS "Average Transfer",
SUM(t.AMOUNT_USD) AS "Total Transfer",
COUNT(DISTINCT t.ORIGIN_FROM_ADDRESS) AS "Address Count",
AVG(p.price) AS "Average Price"
FROM
ethereum.core.ez_token_transfers t
LEFT JOIN (
SELECT
date_trunc('week', HOUR) as price_date,
SYMBOL,
AVG(PRICE) as price
FROM
ethereum.price.ez_prices_hourly
WHERE
TOKEN_ADDRESS IN ('0xc011a73ee8576fb46f5e1c5751ca3b9fe0af2a6f')
GROUP BY
1, 2
) p ON DATE_TRUNC('week', t.BLOCK_TIMESTAMP) = p.price_date AND t.symbol = p.SYMBOL
WHERE
t.CONTRACT_ADDRESS IN ('0xc011a73ee8576fb46f5e1c5751ca3b9fe0af2a6f')
AND t.BLOCK_TIMESTAMP < '2021-03-01'
GROUP BY 1, 2
ORDER BY 1, 2
QueryRunArchived: QueryRun has been archived