Updated 2022-10-17Copy Reference Fork
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 pools as (
select 79 as pool_id, 'REF' as token1, 'NEAR' as token2, 18 as decimal1, 24 as decimal2
),
tokens AS (
select 'wrap.near' as contract_address, 'NEAR' as symbol, 24 as decimal, 'non-stable' as type union
select 'token.v2.ref-finance.near' as contract_address, 'REF' as symbol, 18 as decimal, 'non-stable' as type
),
add_liquidity as (
SELECT
DATE(block_timestamp) as date,
SUM(SPLIT_PART(REGEXP_SUBSTR(logs[0], 'liquidity added \\["(.+)\",' , 1, 1, 'ei'), ' ', 1) / POW(10, decimal1)) as token1,
SUM(SPLIT_PART(REGEXP_SUBSTR(logs[0], ', \"(.+)\"]' , 1, 1, 'ei'), ' ', 1) / POW(10, decimal2)) as token2
FROM near.core.fact_receipts
JOIN tokens t ON t.contract_address ILIKE SPLIT_PART(REGEXP_SUBSTR(logs[0], 'liquidity added \\["(.+)\",' , 1, 1, 'ei'), ' ', 2)
JOIN tokens t2 ON t2.contract_address ILIKE SPLIT_PART(REGEXP_SUBSTR(logs[0], ', \"(.+)\"]' , 1, 1, 'ei'), ' ', 2)
JOIN pools p ON (p.token1 = t.symbol AND p.token2 = t2.symbol)
WHERE receiver_id = 'v2.ref-finance.near'
AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL
AND REGEXP_SUBSTR(logs[0], 'liquidity added \\["(.+)\",' , 1, 1, 'ei') IS NOT NULL
AND (p.token1 ILIKE 'REF' AND p.token2 ILIKE 'NEAR')
GROUP BY 1
),
remove_liquidity as (
SELECT
DATE(t.block_timestamp) as date,
SUM(TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):min_amounts[0] / POW(10, decimal1)) as token1,
SUM(TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):min_amounts[1] / POW(10, decimal2)) as token2
FROM near.core.fact_transactions t
JOIN pools p ON p.pool_id = TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):pool_id
JOIN near.core.fact_receipts h ON h.tx_hash = t.tx_hash
WHERE TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):min_amounts IS NOT NULL
AND (p.token1 ILIKE 'REF' AND p.token2 ILIKE 'NEAR')
AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL
AND h.receiver_id = 'v2.ref-finance.near'
AND REGEXP_SUBSTR(h.logs[0], 'liquidity removed:' , 1, 1, 'ei') IS NOT NULL
GROUP BY 1
Run a query to Download Data