nsa2000near new5
Updated 2023-02-06
999
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 aurora_bridge AS (
SELECT
block_timestamp,
tx_hash,
regexp_substr(status_value, 'Success') as reg_success,
logs,
value,
split(value, ' ') as split,
try_to_numeric(split[1]::string) as numeric_check,
receiver_id,
split[3]::string as sender,
split[5]::string as recipient
FROM near.core.fact_receipts,
table(flatten(input => logs))
WHERE tx_hash IN (SELECT distinct tx_hash FROM near.core.fact_receipts WHERE receiver_Id = 'aurora')
AND reg_success IS NOT NULL
AND numeric_check IS NOT NULL
AND split[0] = 'Transfer'
AND split[2] = 'from'
AND block_timestamp > '2023-01-22' AND block_timestamp < CURRENT_DATE
),
ORACLE AS (
SELECT
date(timestamp) as price_date,
token_contract as oracle_contract,
symbol as oracle_symbol,
avg(price_usd) as usd
FROM near.core.fact_prices
GROUP BY 1,2,3
),
AURORA_TO_NEAR AS (
SELECT
block_timestamp,
value,
tx_hash,
sender,
Run a query to Download Data