connorhCompound Borrowers
    Updated 2021-04-15
    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 '12 months'
    and tx_to_address <> '0xc00e94cb662c3520282e6f5717214004a7f26888' -- exclude the COMP token itself
    ),
    ctok_decimals AS (
    SELECT DISTINCT contract_address, value_numeric AS decimals
    FROM ethereum.reads
    WHERE block_timestamp >= CURRENT_DATE - 30
    AND contract_address IN (select address from ctoks)
    AND function_name = 'decimals'
    ),

    -- 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
    Run a query to Download Data