yasminDAI
Updated 2025-04-08
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_data AS (
SELECT
hour::date AS date_price,
MIN(price) AS ron_price
FROM ronin.price.ez_prices_hourly
WHERE symbol = 'RON'
GROUP BY 1
),
add_lp AS (
SELECT
DATE_TRUNC('WEEK', block_timestamp)::date AS week,
CAST(livequery.utils.udf_hex_to_int(SUBSTRING(data::string, 3, 64)) AS DECIMAL) / 1e6 AS amount_2_usdc,
CAST(livequery.utils.udf_hex_to_int(SUBSTRING(data::string, 67, 64)) AS DECIMAL) / 1e18 AS amount_1_ron
FROM ronin.core.fact_event_logs
WHERE topic_0 = '0x4c209b5fc8ad50758f13e2e1088ba56a560dff690a1c6fef26394f4c03821c4f'
AND contract_address = '0x4f7687affc10857fccd0938ecda0947de7ad3812'
),
remove_lp AS (
SELECT
DATE_TRUNC('WEEK', block_timestamp)::date AS week,
CAST(livequery.utils.udf_hex_to_int(SUBSTRING(data::string, 3, 64)) AS DECIMAL) / 1e6 AS amount_2_usdc,
CAST(livequery.utils.udf_hex_to_int(SUBSTRING(data::string, 67, 64)) AS DECIMAL) / 1e18 AS amount_1_ron
FROM ronin.core.fact_event_logs
WHERE topic_0 = '0xdccd412f0b1252819cb1fd330b93224ca42612892bb3f4f789976e6d81936496'
AND contract_address = '0x4f7687affc10857fccd0938ecda0947de7ad3812'
),
price_week AS (
SELECT
DATE_TRUNC('WEEK', date_price) AS week,
AVG(ron_price) AS avg_weekly_price
FROM price_data
GROUP BY 1
),
QueryRunArchived: QueryRun has been archived