trading as (

select distinct

block_timestamp

, tx_id

, signers[0] as trader

, regexp_substr(logs.value, '[1-9A-Za-z]{40,50}', 1, 1) as profile

, abs(post_balances[accs.index] - pre_balances[accs.index]) / pow(10,9) as amount

, iff(logs.value like '%Bought%', 'BuyShare', 'SellShare') as log_instruction

from solana.core.fact_events

inner join solana.core.fact_transactions

using(block_timestamp, tx_id, succeeded)

inner join lateral flatten (input => log_messages) logs

inner join lateral flatten (input => account_keys) accs

where succeeded

and program_id = '2pi53pUUC5S4zyUU6Wrbe6EfYXS9LNcpikpwPFahtQQw'

and logs.value rlike '^Program log: (Bought|Sold) \\d+ shares.*'

and accs.value :pubkey = regexp_substr(logs.value, '[1-9A-Za-z]{40,50}', 1, 1)

and block_timestamp > '2023-09-23'

),

prices as (

(select

date_trunc('day', recorded_hour) ::date as date_price

, avg(close) as price

from solana.price.ez_token_prices_hourly

where token_address = 'So11111111111111111111111111111111111111112'

and recorded_hour > '2023-09-23'

and recorded_hour < current_date()