connorhcompound liquidators
    Updated 2021-03-30
    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