-- Get token decimals for normalization with fallback

SELECT

TOKEN_ADDRESS,

COALESCE(DECIMALS, 6) AS DECIMALS

FROM solana.price.ez_asset_metadata

WHERE BLOCKCHAIN = 'solana'

),

decoded_data AS (

-- Extract relevant data from decoded events

SELECT

DATE_TRUNC('month', e.BLOCK_TIMESTAMP) AS month,

JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'asset') AS mint_address,

JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'side') AS side,

TRY_CAST(JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'price') AS FLOAT) AS price,

TRY_CAST(JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'size') AS FLOAT) AS size

FROM solana.core.ez_events_decoded e

WHERE e.PROGRAM_ID = 'ZETAxsqBRek56DhiGXrn75yj2NHU3aYUnxvHXpkf3aD'

AND e.BLOCK_TIMESTAMP BETWEEN '2024-01-01' AND '2024-07-31'

AND e.EVENT_TYPE = 'placePerpOrderV4'

AND JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'asset') IS NOT NULL

AND JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'price') IS NOT NULL

AND JSON_EXTRACT_PATH_TEXT(e.DECODED_ARGS, 'size') IS NOT NULL

),

monthly_pnl AS (

-- Calculate P&L normalized by token decimals

SELECT

d.month,

SUM(

CASE

WHEN d.side LIKE '%bid%' THEN

-(COALESCE(d.price, 0) / POW(10, COALESCE(tm.DECIMALS, 6))) *

(COALESCE(d.size, 0) / POW(10, COALESCE(tm.DECIMALS, 6)))

WHEN d.side LIKE '%ask%' THEN

(COALESCE(d.price, 0) / POW(10, COALESCE(tm.DECIMALS, 6))) *

(COALESCE(d.size, 0) / POW(10, COALESCE(tm.DECIMALS, 6)))