connorhcompound liquidators
Updated 2021-03-30
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 ctoks as (
select DISTINCT tx_to_address as address,
tx_to_address_name as ctoken_name
from ethereum.events_emitted
where tx_to_label = 'compound'
and tx_to_label_subtype in ('pool','token_contract','exchange_rate_stored')
and block_timestamp > getdate() - interval '31 days'
and tx_to_address <> '0xc00e94cb662c3520282e6f5717214004a7f26888' -- exclude the COMP token itself
),
-- look up underlying token
underlying as (
select distinct contract_address as address, LOWER(value_string) as token_contract
from ethereum.reads
where contract_address in (select address from ctoks)
and function_name = 'underlying'
and block_timestamp > getdate() - interval '12 months'
UNION
-- this grabs weth for the cETH contract
select contract_address as address, LOWER('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') as token_contract
from ethereum.reads
where contract_address = '0x4ddc2d193948926d02f9b1fe9e1daa0718270ed5'
and block_timestamp > getdate() - interval '12 months'
),
--pull hourly prices for each undelrying
prices as (
select
hour as block_hour,
price as token_price,
decimals as token_decimals,
pr.symbol,
pr.token_address as token_contract, -- this is the undelrying asset
underlying.address -- this is the ctoken
from ethereum.token_prices_hourly as pr
Run a query to Download Data