SELECT

tx_id,

CASE

WHEN value ILIKE '%LendingAccountDeposit%' THEN 'deposit'

WHEN value ILIKE '%LendingAccountBorrow%' THEN 'borrow'

WHEN value ILIKE '%LendingAccountWithdraw%' THEN 'withdraw'

WHEN value ILIKE '%LendingAccountRepay%' THEN 'repay'

END AS action

FROM eclipse.core.fact_transactions,

TABLE(FLATTEN(input => LOG_MESSAGES))

WHERE tx_id IN (

SELECT DISTINCT tx_id

FROM eclipse.core.fact_events_inner

WHERE INSTRUCTION_PROGRAM_ID = 'Astro1oWvtB7cBTwi3efLMFB47WXx7DJDQeoxi235kA'

)

AND (value ILIKE '%LendingAccountDeposit%'

OR value ILIKE '%LendingAccountBorrow%'

OR value ILIKE '%LendingAccountWithdraw%'

OR value ILIKE '%LendingAccountRepay%')

),

prices as

( SELECT date_trunc('day',hour) as day, avg(close) as pr, case

when ASSET_ID = 'ethereum' then 'ETH'

when ASSET_ID = 'usd-coin' then 'USDC'

when ASSET_ID = 'solana' then 'SOL'

when ASSET_ID = 'turbo-eth' then 'tETH'

when ASSET_ID = 'celestia' then 'TIA'

end as Eclipse_cr

from crosschain.price.fact_prices_ohlc_hourly

where hour::date >= '2024-10-01' and

ASSET_ID in ('ethereum', 'solana', 'usd-coin','turbo-eth','celestia')

and PROVIDER = 'coingecko'

GROUP by all ),