nsa2000 Tokens Affected by Listing Announcement
Updated 2022-11-03
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 prices as (
select
date_trunc('day', recorded_at) as p_date,
symbol,
avg(price) as price_usd
from osmosis.core.dim_prices
where recorded_at::date BETWEEN '2022-10-26' and '2022-10-31'
group by 1,2
)
, tx as (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) as date,
TX_ID,
TRADER,
b.PROJECT_NAME as symbolIn,
c.PROJECT_NAME as symbolOut,
FROM_AMOUNT/pow(10, FROM_DECIMAL)*d.price_usd as from_amt,
TO_AMOUNT/pow(10, TO_DECIMAL)*e.price_usd as to_amt
FROM osmosis.core.fact_swaps a
join osmosis.core.dim_labels b on a.FROM_CURRENCY = b.Address --PROJECT_NAME
join osmosis.core.dim_labels c on a.TO_CURRENCY = c.Address
join prices d on b.PROJECT_NAME = d.symbol and date_trunc('day', a.BLOCK_TIMESTAMP) = d.p_date
join prices e on c.PROJECT_NAME = e.symbol and date_trunc('day', a.BLOCK_TIMESTAMP) = e.p_date
WHERE date BETWEEN '2022-10-26' and '2022-10-31'
)
SELECT
date,
symbolIn,
symbolOut,
symbolIn || ' => ' || symbolOut as pair,
COUNT(DISTINCT TX_ID) as tx_cnt,
COUNT(DISTINCT TRADER) as TRADER,
sum(from_amt) as swap_from_amt,
sum(to_amt) as swap_to_amt,
ROW_NUMBER() OVER (partition by date order by tx_cnt desc) rank
from tx
Run a query to Download Data