with pool_Address as ( SELECT DISTINCT DECODED_LOG:pool as pool_ca, DECODED_LOG:token0 as token0_ca, DECODED_LOG:token1 as token1_ca FROM blast.core.ez_decoded_event_logs where tx_hash in ( SELECT TX_HASH from blast.core.fact_traces WHERE TYPE ILIKE 'create%' AND to_address IS NOT NULL AND input IS NOT NULL AND input != '0x' AND tx_status = 'SUCCESS' AND trace_status = 'SUCCESS' and to_address in( SELECT lo2.decoded_log:pool as pool FROM blast.core.ez_decoded_event_logs lo inner join blast.core.ez_decoded_event_logs lo2 on lo2.tx_hash = lo.tx_hash WHERE lo.contract_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.event_name = 'OpenPosition' and lo2.event_name = 'Collect' and lo2.CONTRACT_NAME is not null ) ) and event_name = 'PoolCreated' and TX_STATUS = 'SUCCESS' ), all_token as ( with price as ( with swap_price as ( SELECT symbol, date, token_address, avg(price) as m_price from ( SELECT date_trunc('day', to0.block_timestamp) as date, to0.amount_usd, to1.amount as token_amount, to1.contract_address as token_address, to0.amount_usd / to1.amount as price, to1.symbol as symbol FROM blast.core.ez_token_transfers to0 INNER join blast.core.ez_token_transfers to1 on to0.tx_hash = to1.tx_hash inner join blast.core.fact_transactions fa on fa.tx_hash = to0.tx_hash WHERE to0.CONTRACT_ADDRESS in ( '0x4300000000000000000000000000000000000004', '0x4300000000000000000000000000000000000003' ) and to1.CONTRACT_ADDRESS not in ( '0x4300000000000000000000000000000000000004', '0x4300000000000000000000000000000000000003' ) and (to0.ORIGIN_TO_ADDRESS = to0.FROM_Address) and (to1.origin_from_address = to1.to_Address) and to0.amount_usd != 0 and to0.amount_usd is not null and to1.amount != 0 and to1.amount is not null and fa.to_address = '0x337827814155ecbf24d20231fca4444f530c0555' and fa.origin_function_signature = '0xac9650d8' and fa.STATUS = 'SUCCESS' ) WHERE token_address in ( SELECT DISTINCT token1_ca from pool_Address union all SELECT DISTINCT token1_ca from pool_Address ) group by 1, 2, 3 ), tb_price as( SELECT date_trunc('day', hour) as date, symbol, TOKEN_ADDRESS, avg(price) as price from blast.price.ez_prices_hourly WHERE token_address in ( SELECT token0_ca as token_address from pool_Address union all SELECT token1_ca as token_address from pool_Address ) group by 1, 2, 3 ), Amendment_price as ( SELECT date_trunc('day', hour) as date, 'WETH' as symbol, '0x4300000000000000000000000000000000000004' as TOKEN_ADDRESS, avg(price) as price FROM crosschain.price.ez_prices_hourly where token_address = '0x2170ed0880ac9a755fd29b2688956bd959f933f8' and date_trunc('day', hour) >= '2024-02-27' AND blockchain = 'bsc' and date_trunc('day', hour) not in ( SELECT date FROM tb_price where token_address = '0x4300000000000000000000000000000000000004' ) group by 1, 2, 3 UNION all SELECT date_trunc('day', hour) as date, 'USDB' as symbol, '0x4300000000000000000000000000000000000003' as TOKEN_ADDRESS, 1 as price FROM crosschain.price.ez_prices_hourly where token_address = '0x2170ed0880ac9a755fd29b2688956bd959f933f8' and date_trunc('day', hour) >= '2024-02-27' AND blockchain = 'bsc' and date_trunc('day', hour) not in ( SELECT date FROM tb_price where token_address = '0x4300000000000000000000000000000000000003' ) ) SELECT COALESCE(sp.date, tp.date, a.date) AS date, COALESCE(tp.symbol, sp.symbol, a.symbol) AS symbol, COALESCE( tp.token_address, sp.token_address, a.token_address ) AS token_address, COALESCE(tp.price, sp.m_price, a.price) AS PRICE FROM swap_price sp FULL OUTER JOIN tb_price tp ON sp.date = tp.date AND sp.token_address = tp.token_address full OUTER join Amendment_price a ON sp.date = a.date AND sp.token_address = a.token_address ), token0 as ( SELECT DISTINCT DECODED_LOG:pool as pool_ca, DECODED_LOG:token0 as token0_ca, cn.SYMBOL as SYMBOL0, cn.DECIMALS as DECIMALS0, date, PRICE as price0 FROM blast.core.ez_decoded_event_logs pl LEFT join blast.core.dim_contracts cn on pl.DECODED_LOG:token0 = cn.address LEFT join price pr on pr.TOKEN_ADDRESS = pl.DECODED_LOG:token0 where tx_hash in ( SELECT TX_HASH from blast.core.fact_traces WHERE TYPE ILIKE 'create%' AND to_address IS NOT NULL AND input IS NOT NULL AND input != '0x' AND tx_status = 'SUCCESS' AND trace_status = 'SUCCESS' and to_address in( SELECT lo2.decoded_log:pool as pool FROM blast.core.ez_decoded_event_logs lo inner join blast.core.ez_decoded_event_logs lo2 on lo2.tx_hash = lo.tx_hash WHERE lo.contract_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.event_name = 'OpenPosition' and lo2.event_name = 'Collect' and lo2.CONTRACT_NAME is not null ) ) and event_name = 'PoolCreated' and pl.TX_STATUS = 'SUCCESS' ), token1 as ( SELECT DISTINCT DECODED_LOG:pool as pool_ca, DECODED_LOG:token1 as token1_ca, cn.SYMBOL as SYMBOL1, cn.DECIMALS as DECIMALS1, date, price as price1 FROM blast.core.ez_decoded_event_logs pl LEFT join blast.core.dim_contracts cn on pl.DECODED_LOG:token1 = cn.address LEFT join price pr on pr.TOKEN_ADDRESS = pl.DECODED_LOG:token1 where tx_hash in ( SELECT TX_HASH from blast.core.fact_traces WHERE TYPE ILIKE 'create%' AND to_address IS NOT NULL AND input IS NOT NULL AND input != '0x' AND tx_status = 'SUCCESS' AND trace_status = 'SUCCESS' and to_address in( SELECT lo2.decoded_log:pool as pool FROM blast.core.ez_decoded_event_logs lo inner join blast.core.ez_decoded_event_logs lo2 on lo2.tx_hash = lo.tx_hash WHERE lo.contract_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.event_name = 'OpenPosition' and lo2.event_name = 'Collect' and lo2.CONTRACT_NAME is not null ) ) and event_name = 'PoolCreated' and pl.TX_STATUS = 'SUCCESS' ) SELECT t0.pool_ca, t0.date, token0_ca, SYMBOL0, DECIMALS0, price0, token1_ca, SYMBOL1, DECIMALS1, price1 FROM token0 t0 left join token1 t1 on t1.date = t0.date and t1.pool_ca = t0.pool_ca and t0.pool_ca in ( SELECT pool_ca from pool_Address ) ), all_open_position as ( SELECT lo.tx_hash, lo.block_timestamp, lo.DECODED_LOG:borrower AS user_address, lo.DECODED_LOG:lienId AS lienId, (lo2.DECODED_LOG:amount0 / pow(10, DECIMALS0)) * price0 AS collect_amount0_usd, (lo2.DECODED_LOG:amount1 / pow(10, DECIMALS1)) * price1 AS collect_amount1_usd, (lo2.DECODED_LOG:amount0 / pow(10, DECIMALS0)) AS collect_amount0, (lo2.DECODED_LOG:amount1 / pow(10, DECIMALS1)) AS collect_amount1, lo2.decoded_log:pool AS pool, --substr(tx.input_data, 1, 10) AS function_signature, --livequery.utils.udf_hex_to_int(substr(tx.input_data, 11, 64)) AS tokenId, --livequery.utils.udf_hex_to_int(substr(tx.input_data, 75, 64)) AS marginFrom, --livequery.utils.udf_hex_to_int(substr(tx.input_data, 139, 64)) AS marginTo, price1, price0, symbol0, symbol1, symbol0 || '-' || symbol1 as pair_name, CASE when amount_usd is not null then amount_usd when amount_usd is null and tr.contract_address = t.token0_ca then amount * price0 when amount_usd is null and tr.contract_address = t.token1_ca then amount * price1 else 0 end as collateral_amount_usd, tr.amount as collateral_amount, CASE when tr.contract_address = t.token0_ca then 'Token0' else 'Token1' end as p_collateral, tr.SYMBOL as Collateral_assets, CASE when substr(tx.input_data, 75, 64) != '0000000000000000000000000000000000000000000000000000000000000000' then 'Long' else 'Short' end as trade_type, CASE when tr.contract_address = t.token0_ca then SYMBOL1 else symbol0 end as action_token FROM blast.core.ez_decoded_event_logs lo INNER JOIN blast.core.fact_transactions tx ON lo.tx_hash = tx.tx_hash INNER JOIN blast.core.ez_decoded_event_logs lo2 ON lo2.tx_hash = lo.tx_hash LEFT JOIN pool_Address ad ON ad.pool_ca = lo2.decoded_log:pool left join all_token t on t.pool_ca = lo2.decoded_log:pool and t.date = date_trunc('day', lo.block_timestamp) LEFT join blast.core.ez_token_transfers tr on tr.from_address = lo.origin_from_address and lo.tx_hash = tr.tx_hash and tr.to_address = lo.origin_to_address WHERE lo.contract_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' AND lo.event_name = 'OpenPosition' AND lo2.event_name = 'Collect' AND lo2.CONTRACT_NAME IS NOT NULL --and lo.tx_hash = '0x0c28244b077567cb5988149b041aa6c97975bec0f9578683cb0fee74c16e836e' and tr.to_address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.TX_STATUS = 'SUCCESS' ), all_close_position as ( SELECT lo.tx_hash, lo.block_timestamp, -- lo.DECODED_LOG:borrower AS user_address, lo.DECODED_LOG:token0Refund / pow(10, DECIMALS0) AS token0Refund, lo.DECODED_LOG:token0Refund / pow(10, DECIMALS0) * price0 AS token0Refund_usd, --lo.DECODED_LOG:token1Refund / pow(10, DECIMALS1) AS token1Refund, lo.DECODED_LOG:token1Refund / pow(10, DECIMALS1) * price1 AS token1Refund_usd, lo2.decoded_log:pool AS pool, livequery.utils.udf_hex_to_int(substr(tx.input_data, 11, 64)) AS lienId FROM blast.core.ez_decoded_event_logs lo INNER JOIN blast.core.fact_transactions tx ON lo.tx_hash = tx.tx_hash inner join blast.core.ez_decoded_event_logs lo2 on lo2.tx_hash = lo.tx_hash LEFT JOIN pool_Address ad ON ad.pool_ca = lo2.decoded_log:pool left join all_token t on t.pool_ca = lo2.decoded_log:pool and t.date = date_trunc('day', lo.block_timestamp) WHERE lo.contract_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' AND lo.event_name = 'ClosePosition' and lo2.event_name = 'IncreaseLiquidity' and lo.TX_STATUS = 'SUCCESS' ), all_add_premium as ( SELECT lienId, sum(AMOUNT_USD) as AMOUNT_USD, count(DISTINCT tx_hash) as total_add_number, min(block_timestamp) as fist_add, max(block_timestamp) as last_add from ( SELECT DECODED_LOG:lienId as lienId, lo.TX_HASH as TX_HASH, lo.block_timestamp, AMOUNT, AMOUNT_USD FROM blast.core.ez_decoded_event_logs lo inner join blast.core.ez_token_transfers tr on tr.tx_hash = lo.tx_hash and lo.origin_From_address = tr.from_address where lo.CONTRACT_ADDRESS = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and event_name = 'AddPremium' and to_address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.TX_STATUS = 'SUCCESS' ) GROUP by 1 ), all_Liquidate as ( SELECT lo.origin_from_address as Liquidator, livequery.utils.udf_hex_to_int(substr(tx.input_data, 11, 64)) as lienId, lo.TX_HASH as TX_HASH, lo.block_timestamp, sum(ifnull(AMOUNT_USD, 0)) as amount_usd FROM blast.core.ez_decoded_event_logs lo INNER join blast.core.fact_transactions tx on tx.tx_hash = lo.tx_hash LEFT join blast.core.ez_token_transfers tr on tr.tx_hash = lo.tx_hash and lo.origin_From_address = tr.to_address where lo.CONTRACT_ADDRESS = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.event_name = 'LiquidatePosition' and tr.from_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56' and lo.TX_STATUS = 'SUCCESS' group by 1, 2, 3, 4 ), ranked_prices AS ( SELECT pool_ca, date, price0, price1, ROW_NUMBER() OVER ( PARTITION BY pool_ca ORDER BY date DESC ) AS rn FROM all_token ), agg_data as ( SELECT op.tx_hash as entry_p, op.block_timestamp as open_block_timestamp, op.USER_ADDRESS, op.LIENID, op.COLLECT_AMOUNT0_USD + op.COLLECT_AMOUNT1_USD as Amount_borrowed_usd, op.POOL, op.PAIR_NAME, op.COLLATERAL_AMOUNT_USD, op.TRADE_TYPE || ' ' || op.ACTION_TOKEN as position_type, op.COLLATERAL_ASSETS, pre.AMOUNT_USD as add_premium_amount_usd, pre.total_add_number, pre.fist_add, pre.last_add, cl.tx_hash as close_tx, cl.block_timestamp as close_block_timestamp, token1Refund_usd + token0Refund_usd as claim_amount_usd, liq.block_timestamp as liquidation_block_timestamp, Liquidator, collect_amount0 * rp.price0 as borrow_fund_t0, collect_amount1 * rp.price1 as borrow_fund_t1, SYMBOL0, SYMBOL1, CASE when liq.block_timestamp is null and cl.tx_hash is null then 'Open Positions' when liq.block_timestamp is not null then 'Liquidated Positions' when cl.tx_hash is not null then 'Closed Positions' end as status from all_open_position op LEFT join all_close_position cl on cl.lienId = op.lienId left join all_add_premium pre on pre.lienId = op.lienId left join all_Liquidate liq on liq.lienId = op.lienId LEFT JOIN ranked_prices rp ON op.pool = rp.pool_ca AND rp.rn = 1 where op.price1 is not null and op.price0 is not null ), all_p_pnl as ( SELECT PAIR_NAME, open_block_timestamp, USER_ADDRESS, entry_p, position_type, coalesce(claim_amount_usd, 0) -( COLLATERAL_AMOUNT_USD + coalesce(add_premium_amount_usd, 0) ) as position_profit FROM agg_data WHERE status != 'Open Positions' ) SELECT CASE when position_profit >= 0 then 'Profitable Positions' else 'Losing Positions' end as position_status, CASE when position_profit >= 0 and position_profit < 100 then '100>rPNL>0' when position_profit >= 100 and position_profit < 500 then '500>rPNL>=100' when position_profit >= 500 and position_profit < 1000 then '1000>rPNL>=500' when position_profit >= 1000 then 'rPNL>=1000' WHEN position_profit < 0 AND ABS(position_profit) < 100 THEN '-100= -500 then '-500<=rPNL<-100' when position_profit < -500 and position_profit >= -1000 then '-1000<=rPNL<-500' when position_profit < -1000 then 'rPNL<-1000' end as rPnL, count(entry_p) as "# of Positions" FROM all_p_pnl group by 1, 2

⚫ Particle