nilocum to tokens
Updated 2022-11-05
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
›
⌄
SELECT
DATE_TRUNC ('day',BLOCK_TIMESTAMP) as DATE,
PROJECT_NAME,
COUNT (DISTINCT TX_ID) as SWAPS,
COUNT (DISTINCT TRADER) as TRADERS,
SUM ( CASE WHEN PROJECT_NAME IN ('USTC','ATOM','AXL') THEN ( TO_AMOUNT / pow (10, TO_DECIMAL )) * USD ELSE TO_AMOUNT / pow (10, TO_DECIMAL ) END) as USD_VOLUME_TO,
SUM (SWAPS) over (partition by F.PROJECT_NAME order by DATE ) as CUM_SWAPS,
SUM (TRADERS) over (partition by F.PROJECT_NAME order by DATE ) as CUM_TRADERS,
SUM (USD_VOLUME_TO) over (partition by F.PROJECT_NAME order by DATE ) as CUM_USD_VOLUME_TO
FROM osmosis.core.fact_swaps S
LEFT JOIN osmosis.core.dim_labels F ON ADDRESS = TO_CURRENCY
LEFT JOIN (
SELECT DATE_TRUNC ('DAY',RECORDED_AT) as TIME, SYMBOL AS TOKEN , AVG (PRICE) AS USD FROM osmosis.core.dim_prices GROUP BY 1,2 UNION
SELECT DATE_TRUNC ('DAY',RECORDED_AT) as TIME, 'USTC' AS TOKEN , AVG (PRICE) AS USD FROM osmosis.core.dim_prices WHERE SYMBOL IN ( 'USTC' , 'UST' , 'ustc') GROUP BY 1,2
) P ON ( PROJECT_NAME = TOKEN AND TIME = BLOCK_TIMESTAMP::DATE )
WHERE TX_STATUS = 'SUCCEEDED' and BLOCK_TIMESTAMP >= '2022-05-01'
AND PROJECT_NAME IN ('USTC','ATOM','DAI.axl','AXL','USDC.axl','USDT')
GROUP BY 1,2
Run a query to Download Data