select block_timestamp as time, decoded_log['aum'] as aum, decoded_log['supply'] as supply

from blast.core.fact_decoded_event_logs

where contract_address = lower('0xF0D92907236418Fa8Ee900E384b4c6928f7cADfc') and

event_name in ('AddLiquidity', 'RemoveLiquidity')

),

daily_last_value as (

select distinct

last_value(date_trunc('day', time)) over (PARTITION by DATE(date_trunc('day', time)) order by time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time,

last_value(aum) over (partition by DATE(date_trunc('day', time)) order by time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS aum,

last_value(supply) over (partition by DATE(date_trunc('day', time)) order by time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS supply

from liquidity_service_log

),

trading_fees as (

select time, sum(usd) over (order by time) as accum_fee_usd

from

(

select date_trunc('day', block_timestamp) as time, sum(decoded_log['feeUsd'] * 0.5 )as usd

from blast.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = lower('0x9F1f13eBC178122C3ef6c14FA3A523680563F58b')

and event_name = 'LogSettleTradingFeeAmount'

group by date_trunc('day', block_timestamp)

)

select daily_last_value.time,

(aum / 1e30) / (supply / 1e18) as hlp_price,

from daily_last_value

left join trading_fees on daily_last_value.time = trading_fees.time