symbol as token_A,

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

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

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

case when fee_rate = 'GBtp54LJqqDSWonLT878KWerkJAYqYq4jasZ1UYs8wfD' then '0.01'

when fee_rate = 'GBtp54LJqqDSWonLT878KWerkJAYqYq4jasZ1UYs8wfD' then '0.05'

when fee_rate = 'BGnhGXT9CCt5WYS23zg9sqsAT2MGXkq7VSwch9pML82W' then '1'

when fee_rate = '4kuxsCskbbAvoME1JEdNXJJFWRWP2af2kotyQpmwsVcU' then '0.35'

when fee_rate = '8cdyfqnUEgGeaHvqGyMQZd3Sdax9VyDu74dqVcZWE3t7' then '0.3'

when fee_rate = '62dSkn5ktwY1PoKPNMArZA4bZsvyemuknWUnnQ2ATTuN' then '0.01'

else 0 end as fee_percantage

from solana.core.fact_events a

inner join solana.core.fact_transactions b

on a.tx_id = b.tx_id and a.block_timestamp = b.block_timestamp and regexp_replace(log_messages[1], '^Program log: Instruction: ') = 'InitializePool'

join solana.core.dim_tokens c on a.instruction :accounts[1] ::string = c.TOKEN_ADDRESS

where a.succeeded and a.block_timestamp > '2022-10-12' and b.succeeded and program_id = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc'

and fee_percantage != 0

and a.inner_instruction :instructions[0] :parsed :type ::string = 'createAccount' and a.inner_instruction :instructions[0] :parsed :info :newAccount ::string = instruction :accounts[4] ::string)

,

pools as (

select upper(token_A) as token_A, upper(symbol) as token_B, pool_id, fee_percantage as fee_rate

from tabl1 a join solana.core.dim_tokens b on a.token = b.TOKEN_ADDRESS),

prices as (select date_trunc('week', recorded_hour) as date, token_address, symbol, avg(close) as price_usd

from solana.core.ez_token_prices_hourly group by 1,2,3),

tabl2 as (

select

d.block_timestamp, d.tx_id, SWAPPER, pool_id, fee_rate, b.amount as amount_from, b.mint as mint_from, c.amount as amount_to, c.mint as mint_to

from pools a

inner join solana.core.fact_transfers b on a.pool_id = b.tx_from and b.block_timestamp >= '2022-10-12'

inner join solana.core.fact_transfers c on a.pool_id = c.tx_to and b.tx_id = c.tx_id and c.block_timestamp >= '2022-10-12'

inner join solana.core.fact_swaps d on d.tx_id = b.tx_id and d.tx_id = c.tx_id and d.block_timestamp >= '2022-10-12'