satsihNEAR Swap Outflows Volume Share by Token Out
Updated 2022-11-01
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
daily_average_prices as (
-- Getting daily average usd prices to calculate and impute usd values to swap on NEAR chain
SELECT
DATE_TRUNC('day', timestamp) as day,
symbol,
avg(price_usd) as price
FROM
-- Cleaned price data
(SELECT
timestamp,
symbol,
CASE
WHEN symbol = 'ETH' THEN price_usd * 1000
WHEN symbol = 'WOO' THEN price_usd / 100
ELSE price_usd
END AS price_usd
FROM near.core.fact_prices)
GROUP BY 1, 2
ORDER BY 1 DESC
),
swaps_from_near as (
-- Swaps from NEAR/wNEAR/stNEAR to other tokens on NEAR or Ethereum DEXes with usd values
SELECT
swaps.chain,
swaps.day,
swaps.platform,
swaps.token_in,
swaps.amount_in,
swaps.token_out,
swaps.amount_out,
(prices.price * swaps.amount_out) as swap_size_usd
FROM (SELECT
'NEAR' as chain,
DATE_TRUNC('day', block_timestamp) as day,
platform,
Run a query to Download Data