price AS (

SELECT

hour::date AS block_day,

symbol,

decimals,

avg(price) AS price

FROM

ethereum.core.fact_hourly_token_prices

GROUP BY

1,

2,

3

),

loans AS (

SELECT

date(block_timestamp) AS block_day,

COUNT(tx_hash) AS Loans,

COUNT(DISTINCT borrower) AS Loaners,

SUM(amount_loaned / pow(10, 13) * price) AS Loaned_Volume -- deleting all wrong data

FROM

ethereum.maker.ez_flash_loans a

LEFT JOIN price b ON a.symbol = b.symbol

AND a.block_timestamp::date = b.block_day

WHERE

block_timestamp > '2023-01-01'

AND tx_status = 'SUCCESS'

GROUP BY

1

),

repays AS (

SELECT

date(block_timestamp) AS block_day,

COUNT(DISTINCT tx_hash) AS Repays,

COUNT(DISTINCT payer) AS Repayers,

SUM(amount_paid * price) AS Repaid_Volume