with whirlpools as (select

instruction:accounts[1]::string as token1,

instruction:accounts[2]::string as token2,

instruction:accounts[4]::string as whirlpool_id

from solana.core.fact_events events

inner join solana.core.fact_transactions txs

on events.tx_id = txs.tx_id

and events.block_timestamp = txs.block_timestamp

and regexp_replace(log_messages[1], '^Program log: Instruction: ') = 'InitializePool'

where events.succeeded

and txs.succeeded

and events.block_timestamp > '2022-03-09' -- first pool

and txs.block_timestamp > '2022-03-09' -- first pool

and program_id = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc'

and events.inner_instruction:instructions[0] :parsed :type ::string = 'createAccount'

and events.inner_instruction:instructions[0] :parsed :info :newAccount ::string = instruction :accounts[4] ::string

and whirlpool_id = 'Fvtf8VCjnkqbETA6KtyHYqHm26ut6w184Jqm4MQjPvv7'

),

last_swap as (select distinct

date_trunc('day', block_timestamp) as date,

whirlpool_id,

token1,

token2,

last_value(tx_id) over (partition by whirlpool_id, date order by block_timestamp) as tx_id

from solana.core.fact_swaps swaps

inner join solana.core.fact_transfers transfers

using (tx_id, block_timestamp)

inner join whirlpools

on transfers.tx_from = whirlpools.whirlpool_id

or transfers.tx_to = whirlpools.whirlpool_id

where succeeded

and block_timestamp > '2022-03-09'