Specterdistribution of amount
Updated 2024-11-20
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 aggregate @ https://flipsidecrypto.xyz/studio/queries/50658f69-d457-4cc3-ac94-bc7b4d3e4c36
WITH EthPrice AS (
SELECT
TRUNC(hour, 'day') AS date,
AVG(price) AS price_usd
FROM ethereum.price.ez_prices_hourly
WHERE symbol = 'WETH'
GROUP BY date
),
aofe AS (
SELECT
block_timestamp,
tx_id,
SIGNERS[0] AS minter
FROM eclipse.core.fact_events_inner
WHERE instruction_program_id = 'RariUNM3vz1rwxPg8UJyRAN7rSKXxgd2ncS2ddCa4ZE'
AND EVENT_TYPE = 'mintTo'
AND instruction:parsed:info:mintAuthority = '3bHD7zQGmxVJnxJaSCkzrZyPRUH9Tx5RjkMjUN8fyRTU'
AND SUCCEEDED = 'TRUE'
),
joinaofe AS (
SELECT
a.block_timestamp,
a.tx_id,
a.minter,
t.amount / 1e9 AS amount_eth,
(t.amount / 1e9) * p.price_usd AS amount_usd
FROM eclipse.core.fact_transfers t
JOIN aofe a
ON t.tx_id = a.tx_id
JOIN EthPrice p
ON TRUNC(t.block_timestamp, 'day') = p.date
where-- a.TX_ID = '5CsWFAbPH3x7rQdg6R1PZ4dxzwpF4yvSTf71kHQsTehWVzuf7fmxAHLkHhUzJmHRUUawn3ByEjoM4SUZvn2pd4WZ'
t.tx_to = 'AsSKqK7CkxFUf3KaoQzzr8ZLPm5fFguUtVE5QwGALQQn'
QueryRunArchived: QueryRun has been archived