raffle_participants as (

select

tx_from as user_address

, count(distinct tx_id) as tickets

from solana.core.fact_transfers

where tx_to = '9xMX57VgskjJp3ivUnkWZ4qd5is8spMBPUZCnaz3LhG7'

and mint = 'So11111111111111111111111111111111111111112'

and round(amount, 1) = 19.9

and block_timestamp between '2023-06-29 18:00:00.000' and '2023-06-30 18:00:00.000'

group by 1

),

loans_sharky as (

select distinct

block_timestamp

, tx_id

, signers[0] as user_address

, tx_from

, tx_to

, amount

, regexp_replace(f_logs.value, '^Program log: Instruction: ') as label_action

from solana.core.fact_events

inner join solana.core.fact_transfers

using(tx_id, block_timestamp)

inner join solana.core.fact_transactions

using(tx_id, block_timestamp)

inner join lateral flatten (input => log_messages) as f_logs

inner join raffle_participants