WITH daily_prices AS (

SELECT

token,

date_trunc('day', recorded_hour) AS day,

AVG(close) AS price

FROM flow.price.fact_hourly_prices

WHERE recorded_hour >= '2023-01-01'

GROUP BY token, day

),

LoanListingIndexer AS (

SELECT

BLOCK_TIMESTAMP as listing_date,

DATEADD('DAY', CAST(event_data:term::float/86400 AS INT), block_timestamp) AS listing_expiry_date,

event_data:listingResourceID::string AS listingResourceID,

event_data:amount::float AS amount,

event_data:amount::float * COALESCE(P.price, 1) AS amount_in_USD,

event_data:enabledAutoRepayment::boolean AS enabledAutoRepayment,

event_data:expiresAfter::float/86400 AS expiresAfter_in_Days,

event_data:flowtyStorefrontAddress::string AS flowtyStorefrontAddress,

event_data:flowtyStorefrontID::string AS flowtyStorefrontID,

event_data:interestRate::float * 100 AS interestRate_in_Percent,

event_data:nftID::string AS nftID,

SPLIT(event_data:nftType, '.')[2] AS nftType,

CASE

WHEN event_data:paymentTokenType::string IN ('A.1654653399040a61.FlowToken.Vault') THEN 'Flow'

WHEN event_data:paymentTokenType::string = 'A.b19436aae4d94622.FiatToken.Vault' THEN 'USDC'

ELSE 'Other'

END AS paymentTokenType,

event_data:royaltyRate::float * 100 AS royaltyRate,

event_data:term::float/86400 AS term_in_Days

FROM flow.core.fact_events

LEFT JOIN daily_prices P ON date_trunc('day', block_timestamp) = P.day AND

CASE