KeyrockSolana - Meteora - 3 pools
Updated 2024-05-23
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 pool_1 AS (
SELECT
flattened.value:day_date::DATE AS date_1,
flattened.value:trade_volume::DECIMAL(18, 4) AS trade_volume_1
FROM
(SELECT livequery.live.udf_api(
'https://dlmm-api.meteora.ag/pair/4YVLUZGEhsjfsWuxRbo6h18vL297HYRHTrLVE8bwpyCW/analytic/pair_trade_volume?num_of_days=255'
) AS resp) AS raw_data,
LATERAL FLATTEN(input => PARSE_JSON(raw_data.resp):data) AS flattened
),
pool_2 AS (
SELECT
flattened.value:day_date::DATE AS date_2,
flattened.value:trade_volume::DECIMAL(18, 4) AS trade_volume_2
FROM
(SELECT livequery.live.udf_api(
'https://dlmm-api.meteora.ag/pair/3msVd34R5KxonDzyNSV5nT19UtUeJ2RF1NaQhvVPNLxL/analytic/pair_trade_volume?num_of_days=255'
) AS resp) AS raw_data,
LATERAL FLATTEN(input => PARSE_JSON(raw_data.resp):data) AS flattened
),
pool_3 AS (
SELECT
flattened.value:day_date::DATE AS date_3,
flattened.value:trade_volume::DECIMAL(18, 4) AS trade_volume_3
FROM
(SELECT livequery.live.udf_api(
'https://dlmm-api.meteora.ag/pair/FbkX1h2YTs171cEMa4GrV7XbAiQt5zSmV2CjfYWxXJDP/analytic/pair_trade_volume?num_of_days=255'
) AS resp) AS raw_data,
LATERAL FLATTEN(input => PARSE_JSON(raw_data.resp):data) AS flattened
)
SELECT
p1.date_1,
COALESCE(p1.trade_volume_1, 0) + COALESCE(p2.trade_volume_2, 0) + COALESCE(p3.trade_volume_3, 0) AS Keyrock_total_volume,
0.65*SUM(COALESCE(p1.trade_volume_1, 0) + COALESCE(p2.trade_volume_2, 0) + COALESCE(p3.trade_volume_3, 0)) OVER (ORDER BY p1.date_1) AS Keyrock_cumulative_volume
FROM
pool_1 p1
QueryRunArchived: QueryRun has been archived