-- Count the number of unique signer wallet addresses

SELECT

COUNT(DISTINCT fe.signers[0]::STRING) AS Unique_Signer_Count

FROM

solana.core.fact_events fe

JOIN

solana.core.ez_events_decoded ed

ON fe.tx_id = ed.tx_id

AND fe.program_id = ed.program_id

WHERE

fe.program_id = 'LBUZKhRxPF3XUpBCjp4YzTKgLccjZhTSDM9YuVaPwxo'

AND fe.succeeded = 'true'

AND ed.event_type = 'claimFee'

),

parsed_events AS (

-- Parse JSON to extract mint addresses and token amounts, ensuring data integrity by filtering out incomplete rows

SELECT

fe.block_timestamp AS Event_Timestamp,

fe.tx_id AS Transaction_ID,

fe.succeeded AS Transaction_Success,

ed.event_type AS Event_Type,

fe.program_id AS Program_ID,

fe.instruction AS Instruction_Details,

fe.inner_instruction AS Inner_Instruction_Details,

fe.signers[0]::STRING AS Signer_Wallet_Address,

uc.Unique_Signer_Count,

PARSE_JSON(fe.inner_instruction):instructions[0].parsed.info.mint::STRING AS Primary_Token_Address,

PARSE_JSON(fe.inner_instruction):instructions[1].parsed.info.mint::STRING AS Secondary_Token_Address,

PARSE_JSON(fe.inner_instruction):instructions[0].parsed.info.tokenAmount.uiAmount::NUMBER(38,9) AS Primary_Token_Amount,

PARSE_JSON(fe.inner_instruction):instructions[1].parsed.info.tokenAmount.uiAmount::NUMBER(38,9) AS Secondary_Token_Amount

FROM

solana.core.fact_events fe

JOIN

solana.core.ez_events_decoded ed