connorhCurve Swaps
Updated 2021-11-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 pool_tokens AS (
SELECT
DISTINCT
date_trunc('hour',block_timestamp) AS blockhour,
LOWER(inputs:_pool::STRING) AS pool_add,
(SPLIT(LOWER(VALUE_STR),'^')) AS coins
FROM SILVER.ETHEREUM_READS
WHERE contract_name='Vyper_contract'
AND contract_address IN ('0x0959158b6040d32d04c301a72cbfd6b39e21c9ae',LOWER('0xfD6f33A0509ec67dEFc500755322aBd9Df1bD5B8'))
AND function_name = 'get_underlying_coins'
--QUALIFY (row_number() OVER (partition by token_address order by weight desc)) = 1
AND block_timestamp >= CURRENT_DATE - 60
), pool_tokens_parsed AS (
SELECT
blockhour,
pool_add,
value::STRING AS coins,
(row_number() OVER (partition by pool_add,blockhour order by blockhour desc) - 1 ) AS index
FROM pool_tokens,
Table(Flatten(pool_tokens.coins))
WHERE value::STRING <> '0x0000000000000000000000000000000000000000'
), curve_swaps_raw AS (
SELECT
block_id,block_timestamp,tx_id,event_index,
event_inputs:buyer::STRING AS swapper,
contract_address AS pool_name,
event_inputs:bought_id AS index_in,
event_inputs:tokens_bought AS amount_in,
event_inputs:sold_id AS index_out,
event_inputs:tokens_sold AS amount_out
FROM ethereum.events_emitted
WHERE event_name = 'TokenExchange'
AND block_timestamp >= CURRENT_DATE - 60
AND event_inputs:buyer IS NOT NULL
Run a query to Download Data