SELECT

DISTINCT

date_trunc('day',block_timestamp) AS balance_date,

contract_address AS lending_pool_add,

CASE

WHEN contract_address IN (LOWER('0x057835Ad21a177dbdd3090bB1CAE03EaCF78Fc6d'),LOWER('0xc443AD9DDE3cecfB9dfC5736578f447aFE3590ba')) THEN 'DataProvider'

ELSE 'LendingPool' END AS

lending_pool_type,

function_name,

CASE

WHEN contract_address IN (LOWER('0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9'),LOWER('0x057835Ad21a177dbdd3090bB1CAE03EaCF78Fc6d')) THEN 'Aave V2'

WHEN contract_address IN (LOWER('0x7937d4799803fbbe595ed57278bc4ca21f3bffcb'),LOWER('0xc443AD9DDE3cecfB9dfC5736578f447aFE3590ba')) THEN 'Aave AMM'

ELSE 'Aave V1'

END AS aave_version,

inputs,

(SPLIT(LOWER(VALUE_STRING),'^')) AS coins

FROM flipside_prod_db.ethereum.reads

WHERE

contract_address = LOWER('0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9')

AND block_timestamp::date >= CURRENT_DATE - 10

ORDER BY balance_date DESC

-- first split them into a log format where we have one row per field per read

), long_format AS (

SELECT

DISTINCT

balance_date,

aave_version,

lending_pool_add,

lending_pool_type,

CASE WHEN LOWER(COALESCE(inputs:address,inputs:_reserve)) = '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' THEN '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

ELSE LOWER(COALESCE(inputs:address,inputs:_reserve)) END AS reserve_token,