Specterdistribution of withdraw volume
Updated 2024-11-27
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 distribution of deposit volume @ https://flipsidecrypto.xyz/studio/queries/e45eb2fa-c548-4d83-8274-fd6ebf6a2174
WITH ETHprice AS (
SELECT
TRUNC(hour, 'day') AS day,
AVG(price) AS price
FROM ethereum.price.ez_prices_hourly
WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
GROUP BY day
),
-- Aggregated Deposits
Claims AS (
SELECT ft.instruction:parsed:info:destination AS users,
SUM((ft.instruction:parsed:info:lamports / 1e9) * ep.price) AS total_claim_usd,
COUNT(DISTINCT tx_id) AS total_claims,
FROM eclipse.core.fact_events_inner ft
JOIN ETHprice ep ON TRUNC(ft.block_timestamp, 'day') = ep.day
WHERE
ft.instruction_program_id = 'DcZMKcjz34CcXF1vx7CkfARZdmEja2Kcwvspu1Zw6Zmn'
AND ft.event_type = 'transfer'
AND ft.signers[0] = ft.instruction:parsed:info:destination
AND succeeded = 'TRUE'
GROUP BY users
)
-- Final Aggregated Insights by Volume
SELECT
CASE
WHEN total_claim_usd < 50 THEN '< $100'
WHEN total_claim_usd BETWEEN 50 AND 200 THEN '$50 - $200'
WHEN total_claim_usd BETWEEN 200 AND 500 THEN '$200 - $5,000'
WHEN total_claim_usd BETWEEN 500 AND 1000 THEN '$5000 - $1000'
ELSE '> $1,000'
END AS volume_range,
COUNT(DISTINCT users) AS user_count
QueryRunArchived: QueryRun has been archived