binhachon31. [Hard] Will I actually get liquidated though? - Base table
Updated 2021-11-16
999
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
›
⌄
-- from https://aave.github.io/aave-addresses/mainnet.json
WITH json_data as (
SELECT
' {"proto":[{"aTokenAddress":"0x3Ed3B47Dd13EC9a98b44e6204A523E766B225811","aTokenSymbol":"aUSDT","stableDebtTokenAddress":"0xe91D55AB2240594855aBd11b3faAE801Fd4c4687","variableDebtTokenAddress":"0x531842cEbbdD378f8ee36D171d6cC9C4fcf475Ec","symbol":"USDT","address":"0xdAC17F958D2ee523a2206206994597C13D831ec7","decimals":6},{"aTokenAddress":"0x9ff58f4fFB29fA2266Ab25e75e2A8b3503311656","aTokenSymbol":"aWBTC","stableDebtTokenAddress":"0x51B039b9AFE64B78758f8Ef091211b5387eA717c","variableDebtTokenAddress":"0x9c39809Dec7F95F5e0713634a4D0701329B3b4d2","symbol":"WBTC","address":"0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599","decimals":8},{"aTokenAddress":"0x030bA81f1c18d280636F32af80b9AAd02Cf0854e","aTokenSymbol":"aWETH","stableDebtTokenAddress":"0x4e977830ba4bd783C0BB7F15d3e243f73FF57121","variableDebtTokenAddress":"0xF63B34710400CAd3e044cFfDcAb00a0f32E33eCf","symbol":"WETH","address":"0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2","decimals":18},{"aTokenAddress":"0x5165d24277cD063F5ac44Efd447B27025e888f37","aTokenSymbol":"aYFI","stableDebtTokenAddress":"0xca823F78C2Dd38993284bb42Ba9b14152082F7BD","variableDebtTokenAddress":"0x7EbD09022Be45AD993BAA1CEc61166Fcc8644d97","symbol":"YFI","address":"0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e","decimals":18},{"aTokenAddress":"0xDf7FF54aAcAcbFf42dfe29DD6144A69b629f8C9e","aTokenSymbol":"aZRX","stableDebtTokenAddress":"0x071B4323a24E73A5afeEbe34118Cd21B8FAAF7C3","variableDebtTokenAddress":"0x85791D117A392097590bDeD3bD5abB8d5A20491A","symbol":"ZRX","address":"0xE41d2489571d322189246DaFA5ebDe1F4699F498","decimals":18},{"aTokenAddress":"0xB9D7CB55f463405CDfBe4E90a6D2Df01C2B92BF1","aTokenSymbol":"aUNI","stableDebtTokenAddress":"0xD939F7430dC8D5a427f156dE1012A56C18AcB6Aa","variableDebtTokenAddress":"0x5BdB050A92CADcCfCDcCCBFC17204a1C9cC0Ab73","symbol":"UNI","address":"0x1f9840a85d5aF5bf1D1762F925BDADdC4201F984","decimals":18},{"aTokenAddress":"0xFFC97d72E13E01096502Cb8Eb52dEe56f74DAD7B","aTokenSymbol":"aAAVE","stableDebtTokenAddress":"0x079D6a3E844BcECf5720478A718Edb6575362C5f","variableDebtTokenAddress":"0xF7DBA49d571745D9d7fcb56225B05BEA803EBf3C","symbol":"AAVE","address":"0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9","decimals":18},{"aTokenAddress":"0x05Ec93c0365baAeAbF7AefFb0972ea7ECdD39CF1","aTokenSymbol![]()
![]()
),
aave_tokens as (
SELECT
lower(value:aTokenAddress::string) as atoken_address,
value:aTokenSymbol::string as atoken_symbol,
lower(value:stableDebtTokenAddress::string) as stable_debt_token_address,
lower(value:variableDebtTokenAddress::string) as variable_debt_token_address,
value:symbol::string as symbol,
lower(value:address::string) as address,
value:decimals::decimal as decimals
from json_data, lateral flatten (input => parse_json(aave_tokens_json):proto)
),
aave_tokens_price AS (
SELECT
atoken_address,
stable_debt_token_address,
variable_debt_token_address,
atoken_symbol,
aave_tokens.symbol,
tok_price.price as price,
hour,
row_number() over(partition by atoken_address order by hour desc) as rank
FROM aave_tokens
INNER JOIN ethereum.token_prices_hourly tok_price ON tok_price.token_address = aave_tokens.atoken_address
WHERE hour > CURRENT_DATE - 7
QUALIFY rank = 1
),
aave_borrowers as (
SELECT
user_address,
balance_date,
row_number() over( partition by user_address order by balance_date desc ) as rank
Run a query to Download Data