Query editor
with arbitrum_trades as (SELECT *,count(evt_tx_hash) over (partition by trader) as number_of_transactionsFROM (SELECTDATE_TRUNC('day', open_positions.evt_block_time) AS tx_day,evt_block_time,trader,FROM_HEX(JSON_VALUE(position, 'strict $.soldAsset')) AS token_sell,FROM_HEX(JSON_VALUE(position, 'strict $.positionAsset')) AS token_buy,CAST(JSON_QUERY(position, 'strict $.positionAmount') AS UINT256) AS amount,FROM_HEX(JSON_VALUE(position, 'strict $.positionAsset')) AS amount_asset,evt_tx_hash,'Open' AS action,'arbitrum' as blockchainFROM primex_arbitrum.PositionManager_evt_OpenPosition AS open_positionsUNIONSELECTDATE_TRUNC('day', close_positions.evt_block_time) AS tx_day,evt_block_time,trader,positionAsset AS token_sell,soldAsset AS token_buy,decreasePositionAmount AS amount,positionAsset AS amount_asset,evt_tx_hash,'Close' AS action,'arbitrum' as blockchainFROM primex_arbitrum.PositionManager_evt_ClosePosition AS close_positions)),polygon_trades as (SELECT *,count(evt_tx_hash) over (partition by trader) as number_of_transactionsFROM (SELECTDATE_TRUNC('day', open_positions.evt_block_time) AS tx_day,evt_block_time,trader,FROM_HEX(JSON_VALUE(position, 'strict $.soldAsset')) AS token_sell,FROM_HEX(JSON_VALUE(position, 'strict $.positionAsset')) AS token_buy,CAST(JSON_QUERY(position, 'strict $.positionAmount') AS UINT256) AS amount,FROM_HEX(JSON_VALUE(position, 'strict $.positionAsset')) AS amount_asset,evt_tx_hash,'Open' AS action,'polygon' as blockchainFROM primex_polygon.PositionManager_evt_OpenPosition AS open_positionsUNIONSELECTDATE_TRUNC('day', close_positions.evt_block_time) AS tx_day,evt_block_time,trader,positionAsset AS token_sell,soldAsset AS token_buy,decreasePositionAmount AS amount,positionAsset AS amount_asset,evt_tx_hash,'Close' AS action,'polygon' as blockchain