Updated 2023-01-31
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 txns AS (
SELECT
BLOCK_TIMESTAMP AS time
,CASE WHEN TO_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') AND CONTRACT_ADDRESS = lower('0x6B175474E89094C44Da98b954EedeAC495271d0F')
THEN RAW_AMOUNT / 1e18
WHEN FROM_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') AND CONTRACT_ADDRESS = lower('0x6B175474E89094C44Da98b954EedeAC495271d0F')
THEN -RAW_AMOUNT / 1e18
END AS dai
,CASE WHEN TO_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') AND CONTRACT_ADDRESS = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
THEN RAW_AMOUNT / 1e6
WHEN FROM_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') AND CONTRACT_ADDRESS = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
THEN -RAW_AMOUNT / 1e6
END AS usdc
,CASE WHEN TO_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') AND CONTRACT_ADDRESS = lower('0xdAC17F958D2ee523a2206206994597C13D831ec7')
THEN RAW_AMOUNT / 1e6
WHEN FROM_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') AND CONTRACT_ADDRESS = lower('0xdAC17F958D2ee523a2206206994597C13D831ec7')
THEN -RAW_AMOUNT / 1e6
END AS usdt
FROM ethereum.core.fact_token_transfers
WHERE (TO_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7') OR FROM_ADDRESS = lower('0xbEbc44782C7dB0a1A60Cb6fe97d0b483032FF1C7'))
AND CONTRACT_ADDRESS IN (lower('0x6B175474E89094C44Da98b954EedeAC495271d0F'),lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'),lower('0xdAC17F958D2ee523a2206206994597C13D831ec7'))
),
pool_data AS (
SELECT
*
,dai_cum + usdc_cum + usdt_cum AS total_tvl
,(dai_cum + usdc_cum + usdt_cum) / 1000000 AS total_tvl_for_counter
,dai_cum / (dai_cum + usdc_cum + usdt_cum) AS dai_p
,usdc_cum / (dai_cum + usdc_cum + usdt_cum) AS usdc_p
,usdt_cum / (dai_cum + usdc_cum + usdt_cum) AS usdt_p
,usdc_cum / (dai_cum + usdc_cum + usdt_cum) * 100 AS usdc_p_for_counter
,usdt_cum / (dai_cum + usdc_cum + usdt_cum) * 100 AS usdt_p_for_counter
FROM (
SELECT
*
Run a query to Download Data