config as (

select

timestamp '2024-04-24 00:01' as start_time,

timestamp '2024-05-08 23:59' as end_time

),

account_connection as (

select distinct * from

(

select

DECODED_LOG['primaryAccount'] as account,

DECODED_LOG['subAccount'] as subAccount,

from blast.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = lower('0x0a8D9c0A4a039dDe3Cb825fF4c2f063f8B54313A') -- tradeService

and EVENT_NAME = 'LogIncreasePosition'

)

),

first_appearance_in_comp as (

select distinct primaryAccount, subAccount, marketIndex,

first_value(evt_block_time) over (partition by primaryAccount, subAccount, marketIndex order by evt_block_time asc) as first_valid_open,

first_value(evt_tx_hash) over (partition by primaryAccount, subAccount, marketIndex order by evt_block_time asc) as first_valid_tx,

first_value(evt_index) over (partition by primaryAccount, subAccount, marketIndex order by evt_block_time, evt_index asc) as first_evt_index

from (

select

DECODED_LOG['primaryAccount'] as primaryAccount,

DECODED_LOG['subAccount'] as subAccount,

DECODED_LOG['marketIndex'] as marketIndex,

DECODED_LOG['size'] as size,

DECODED_LOG['increasedSize'] as increasedSize,

DECODED_LOG['realizedPnl'] as realizedPnl,

DECODED_LOG['price'] as price,

DECODED_LOG['avgEntryPrice'] as avgEntryPrice,

BLOCK_TIMESTAMP as evt_block_time,

EVENT_INDEX as evt_index,