Abolfazl_771025top 10 token that swapped in by volume (USD) at autumn
Updated 2022-10-20
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 swaps_main AS (SELECT
block_timestamp,
tx_hash,
split(value,' ') as split,
split[0]::string as stat,
replace(split[2],',') as token_in1,
replace(split[5],',') as token_out1,
split[1] as amount_in1,
split[4] as amount_out1
FROM near.core.fact_receipts,table(flatten(input => logs))
where receiver_id = 'v2.ref-finance.near'
),swaps AS (SELECT
block_timestamp,
date(block_timestamp) as swap_date,
tx_hash,
tx_signer,
nvl(a.symbol, token_in1) as token_in,
nvl(b.symbol, token_out1) as token_out,
(try_to_numeric(amount_in1::string))/pow(10,a.decimals) as amount_in,
(try_to_numeric(amount_out1::string))/pow(10,b.decimals) as amount_out
FROM swaps_main
LEFT JOIN near.core.dim_token_labels a ON a.token_contract = token_in1
LEFT JOIN near.core.dim_token_labels b ON b.token_contract = token_out1
LEFT JOIN (SELECT tx_signer,tx_hash as tx_h FROM near.core.fact_transactions) ON tx_h = tx_hash
WHERE stat = 'Swapped'
),ORACLE_PRICES AS (SELECT
date(block_timestamp) as price_date,
token_in as oracle_symbol,
sum(amount_out)/sum(amount_in) as price_usd
FROM swaps
GROUP BY 1,2
),swaps_usd AS (SELECT
swap_date,
tx_hash,
tx_signer,
Run a query to Download Data