binhachon86. [Easy] Currency Interactions - Percentage of top 100 on volume
Updated 2021-10-10
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 TRADING_TABLE AS(
SELECT TRADER, TOKEN_0_CURRENCY AS SYMBOL, TOKEN_0_AMOUNT AS AMOUNT, TOKEN_0_AMOUNT_USD AS AMOUNT_USD FROM terra.swaps
WHERE BLOCK_TIMESTAMP >= GETDATE() - INTERVAL'90 days'
AND AMOUNT_USD IS NOT NULL
UNION ALL
SELECT TRADER, TOKEN_1_CURRENCY AS SYMBOL, TOKEN_1_AMOUNT AS AMOUNT, TOKEN_1_AMOUNT_USD AS AMOUNT_USD FROM terra.swaps
WHERE BLOCK_TIMESTAMP >= GETDATE() - INTERVAL'90 days'
AND AMOUNT_USD IS NOT NULL
),
VOLUME_TABLE AS(
SELECT TRADER, SYMBOL, SUM(AMOUNT) AS AMOUNT, SUM(AMOUNT_USD) AS AMOUNT_USD FROM TRADING_TABLE
GROUP BY TRADER, SYMBOL
),
VOLUME_TABLE_WITH_INDEX AS (
SELECT TRADER, SYMBOL, AMOUNT, AMOUNT_USD, row_number() over (partition by SYMBOL order by AMOUNT_USD desc) as INDEX FROM VOLUME_TABLE
),
FREQUENCY_TABLE AS(
SELECT TRADER, SYMBOL, COUNT(SYMBOL) AS FREQUENCY FROM TRADING_TABLE
GROUP BY TRADER, SYMBOL
),
FREQUENCY_TABLE_WITH_INDEX AS (
SELECT TRADER, SYMBOL, FREQUENCY, row_number() over (partition by SYMBOL order by FREQUENCY desc) as INDEX FROM FREQUENCY_TABLE
),
FINAL_TABLE AS(
SELECT VOLUME_TABLE_WITH_INDEX.TRADER AS TRADER, VOLUME_TABLE_WITH_INDEX.SYMBOL AS SYMBOL,
VOLUME_TABLE_WITH_INDEX.INDEX AS VOLUME_INDEX, AMOUNT, AMOUNT_USD,
FREQUENCY_TABLE_WITH_INDEX.INDEX AS FREQUENCY_INDEX, FREQUENCY
FROM VOLUME_TABLE_WITH_INDEX LEFT JOIN FREQUENCY_TABLE_WITH_INDEX
ON VOLUME_TABLE_WITH_INDEX.TRADER = FREQUENCY_TABLE_WITH_INDEX.TRADER AND VOLUME_TABLE_WITH_INDEX.SYMBOL = FREQUENCY_TABLE_WITH_INDEX.SYMBOL
),
TOTAL_TABLE AS(
SELECT SYMBOL, SUM(AMOUNT_USD) AS TOTAL_USD, SUM(FREQUENCY) AS TOTAL_FREQUENCY FROM FINAL_TABLE
WHERE SYMBOL IS NOT NULL
GROUP BY SYMBOL
),
TOP_100_FREQUENCY AS(
Run a query to Download Data