SELECT

block_timestamp AS date,

tx_hash,

decoded_log:loan:borrower::string AS Borrower,

decoded_log:loan:debt[0]:token::string AS Borrowed_Token,

decoded_log:loan:debt[0]:amount::int / pow(10, 9) AS Borrowed_Amount,

decoded_log:loan:collateral[0]:token::string AS Collateral,

decoded_log:loan:collateral[0]:amount::int / pow(10, 6) AS Collateral_Amount

FROM base.core.fact_decoded_event_logs

WHERE contract_address = LOWER('0x00000000000cC7ba78E64E86B2Bd59B1ae7F569E')

AND event_name = 'Open'

),

price AS (

SELECT

hour,

price,

symbol,

token_address

FROM base.price.ez_prices_hourly

ld.date AS block_timestamp,

ld.tx_hash,

ld.borrower,

pb.symbol AS borrowed_symbol,

ld.borrowed_token,

ld.borrowed_amount * pb.price AS usd_borrowed,

pc.symbol AS collateral_symbol,

ld.collateral,

ld.collateral_amount * pc.price AS usd_collateral

FROM loan_details ld

JOIN price pb

ON DATE_TRUNC('hour', ld.date) = pb.hour