-- SELECT AAVE_MARKET, SUM(BORROWED_TOKENS) AS BORROWED_TOKENS, SUM(BORROWED_USD) AS BORROWED_USD, SUM(FLASHLOAN_AMOUNT) AS FLASHLOAN_AMOUNT, SUM(FLASHLOAN_AMOUNT_USD) AS FLASHLOAN_AMOUNT_USD,

-- SYMBOL, VOLUME_MAGNITUDE

SELECT SUM(BORROWED_USD) AS BORROWED_USD, SUM(FLASHLOAN_AMOUNT_USD) AS FLASHLOAN_AMOUNT_USD, VOLUME_MAGNITUDE, SUM(BORROWED_USD) / SUM(FLASHLOAN_AMOUNT_USD) AS RATIO

FROM

((SELECT AAVE_MARKET, AAVE_TOKEN, BORROWED_TOKENS, BORROWED_USD, 0 AS FLASHLOAN_AMOUNT, 0 AS FLASHLOAN_AMOUNT_USD, BORROWER_ADDRESS AS ADDRESS, AAVE_VERSION,

CASE WHEN SYMBOL IN ('USDC', 'USDT', 'DAI', 'SUSD', 'BUSD', 'TUSD', 'GUSD', 'FEI') THEN 'STABLE_COIN'

-- WHEN SYMBOL IN ('AAVE', 'LINK', 'YFI', 'UNI', 'CRV', 'SNX', 'MKR', 'REN', 'BAL', 'ZRX', 'KNC', 'XSUSHI') THEN 'DEFI'

ELSE 'OTHER' END AS SYMBOL,

CASE WHEN BORROWED_USD < 10000 THEN '1. 0-10k'

WHEN 100000 > BORROWED_USD >= 10000 THEN '2. 10k-100k'

WHEN 1000000 > BORROWED_USD >= 100000 THEN '3. 100k-1M'

WHEN 10000000 > BORROWED_USD >= 1000000 THEN '4. 1M-10M'

WHEN 100000000 > BORROWED_USD >= 10000000 THEN '5. 10M-100M'

WHEN BORROWED_USD >= 100000000 THEN '6. Over 100M' END AS VOLUME_MAGNITUDE

FROM

(SELECT AAVE_MARKET, AAVE_TOKEN, BORROWED_TOKENS,

CASE WHEN SYMBOL IN ('BUSD', 'TUSD', 'DAI', 'USDC', 'SUSD', 'USDT') AND (TOKEN_PRICE IS NULL) THEN BORROWED_TOKENS ELSE BORROWED_USD END AS BORROWED_USD,

BORROWER_ADDRESS, BORROW_RATE_MODE, LENDING_POOL_CONTRACT, AAVE_VERSION, TOKEN_PRICE, SYMBOL

FROM aave.borrows

WHERE BLOCK_TIMESTAMP > '2020-05-05T03:13:18Z')

WHERE BORROWED_USD > 0)

UNION ALL

(SELECT AAVE_MARKET, AAVE_TOKEN, 0 AS BORROWED_TOKENS, 0 AS BORROWED_USD, FLASHLOAN_AMOUNT, FLASHLOAN_AMOUNT_USD, INITIATOR_ADDRESS AS ADDRESS, AAVE_VERSION,