Updated 2025-02-11
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 price_cte AS (
SELECT
DISTINCT symbol,
name,
price,
decimals,
DATE_TRUNC('day', hour) AS price_date
FROM ronin.price.ez_prices_hourly
),
swap_cte AS (
SELECT
tx_hash,
origin_from_address AS swapper,
contract_name,
COALESCE(NULLIF(decoded_log:_amount0In, 0), decoded_log:_amount1In)/1e18 AS raw_amount,
DATE_TRUNC('day', block_timestamp) AS swap_date
FROM ronin.core.ez_decoded_event_logs
WHERE event_name = 'Swap'
),
volume_cte AS (
SELECT
s.tx_hash,
s.swapper,
s.raw_amount,
p.symbol,
s.swap_date,
(s.raw_amount / POWER(10, p.decimals)) * p.price AS volume_usd
FROM swap_cte s
JOIN price_cte p
ON SPLIT_PART(s.contract_name, ' - ', 1) ILIKE '%' || p.name || '%'
AND s.swap_date = p.price_date
)
SELECT
tx_hash,
swapper,
QueryRunArchived: QueryRun has been archived