with

lucky_txs as (

select distinct

fact_events.block_timestamp

, fact_events.tx_id

, fact_transfers.mint

, last_value(fact_transfers.tx_to) over (partition by fact_events.tx_id order by fact_transfers.index) as user_address

from solana.core.fact_events

inner join solana.core.fact_transfers

using(block_timestamp, tx_id)

inner join solana.core.dim_labels

on fact_transfers.mint = dim_labels.address

and dim_labels.label = 'degen ape academy'

where fact_events.succeeded

and fact_events.program_id = 'LUCK57mxzZiRGF2PdHAY79P6tZ8Apsi381tKvBrTdqk' -- ME Lucky Buy

and block_timestamp > '2023-09-12'

and block_timestamp > current_date() - 7

),

lucky_wins as (

select

block_timestamp

, tx_id

, mint

, user_address

, parse_json(regexp_replace(f_prices.value, '^Program log: ')) as log_prices

, parse_json(regexp_replace(f_fees.value, 'Program log: Fees paid: ')) as log_fees

, log_prices :price / pow(10,9) as price

, log_prices :price / pow(10,9) * 0.015 as fee_me

, log_prices :royalty / pow(10,9) as royalty

, log_fees :creator / pow(10,9) as fee_creator