with tabl1 as (select

symbol as token_A,

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

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

instruction :accounts[5] ::string as vault_a,

instruction :accounts[6] ::string as vault_b

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-04-25' and b.succeeded and program_id = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc'

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 concat(upper(token_A), '/', upper(symbol)) as pool, pool_id, vault_a, vault_b

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

increase_df as (

select date, count(DISTINCT tx_id) as number_increasing, count(DISTINCT users) as users_increasing, round(number_increasing/users_increasing) as increase_per_user from (

date_trunc('week', block_timestamp) as date,tx_id, signers[0] as users

from solana.core.fact_events a

inner join pools b

on a.inner_instruction :instructions[0] :parsed :info :destination ::string = b.vault_a and a.inner_instruction :instructions[1] :parsed :info :destination ::string = b.vault_b

and instruction :accounts[7] ::string = b.vault_a and instruction :accounts[8] ::string = b.vault_b

where succeeded != 'FALSE' and program_id = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc' and date >= '2022-04-25'

and inner_instruction :instructions[0] :parsed :type ::string = 'transfer' and inner_instruction :instructions[1] :parsed :type ::string = 'transfer')

group by 1 order by 1 ),

decrease_df as (

select date, count(DISTINCT tx_id) as number_decreasing, count(DISTINCT users) as users_decreasing, round(number_decreasing/users_decreasing) as decrease_per_user from (

select date_trunc('week', block_timestamp) as date, tx_id, signers[0] as users

from solana.core.fact_events a

inner join pools b

on a.inner_instruction :instructions[0] :parsed :info :source ::string = b.vault_a and a.inner_instruction :instructions[1] :parsed :info :source ::string = b.vault_b