lock_txs as (

select

block_timestamp

, tx_id

, regexp_replace(f_logs.value, '^Program log: Fees ') as fees_string

, parse_json(fees_string) :premium ::number / pow(10, 9) as premium

, parse_json(fees_string) :premium_net_fees ::number / pow(10, 9) as premium_net_fees

, parse_json(fees_string) :total_fee ::number / pow(10, 9) as total_fee

from solana.core.fact_events

inner join solana.core.fact_transactions

using(tx_id, block_timestamp, succeeded, block_id)

inner join lateral flatten (input => fact_transactions.log_messages) f_logs

where succeeded

and fact_events.program_id ='TLoCKic2wGJm7VhZKumih4Lc35fUhYqVMgA4j389Buk'

and array_contains('Program log: Instruction: LockOrder' ::variant, log_messages)

and f_logs.value rlike '^Program log: Fees \{.*\}'

and fact_transactions.log_messages[f_logs.index -3] = 'Program TLoCKic2wGJm7VhZKumih4Lc35fUhYqVMgA4j389Buk invoke [1]'

and fact_transactions.log_messages[f_logs.index -2] = 'Program log: Instruction: LockOrder'

and block_timestamp >= '2024-01-22 16:00:00'

and block_timestamp < '2024-01-23 16:00:00'

),

aggregated as (

select

date_trunc('hour', block_timestamp) as date

, count(1) as orders

, sum(premium) as premium