whirlpools as (

select

events.block_timestamp,

events.tx_id,

instruction :accounts[4] ::string as whirlpool_id,

instruction :accounts[7] ::string as fee_tier_acc,

case

when fee_tier_acc = '5cyhCta55L7DrWB9bi9Uwh1f98ag71wScRbGqcSAbyGN' then 0

when fee_tier_acc = 'GaiUH8finbmCZRcfmepKVP1xVx66fd81BmoDvoVGhA3s' then 0

when fee_tier_acc = '62dSkn5ktwY1PoKPNMArZA4bZsvyemuknWUnnQ2ATTuN' then 0.01

when fee_tier_acc = 'GBtp54LJqqDSWonLT878KWerkJAYqYq4jasZ1UYs8wfD' then 0.05

when fee_tier_acc = 'HYbauzbeSCCmJgt5Cf9npFXwQiDphFNQCWVE3tzPf9nV' then 0.25

when fee_tier_acc = 'HT55NVGVTjWmWLjV7BrSMPVZ7ppU8T2xE5nCAZ6YaGad' then 0.3

when fee_tier_acc = '8cdyfqnUEgGeaHvqGyMQZd3Sdax9VyDu74dqVcZWE3t7' then 0.3

when fee_tier_acc = '4kuxsCskbbAvoME1JEdNXJJFWRWP2af2kotyQpmwsVcU' then 0.35

when fee_tier_acc = 'BGnhGXT9CCt5WYS23zg9sqsAT2MGXkq7VSwch9pML82W' then 1

else 0

end as fee_pct

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'