Query editor
WITH BuySell_negrisk AS (SELECTtakerOrderMaker AS user_address,CASEWHEN makerAssetId = 0 THEN makerAmountFilled / 1e6WHEN takerAssetId = 0 THEN takerAmountFilled / 1e6END AS "Amount USDC", -- in OrdersMatched event, makerAssetId = 0 indicates a 'buy' and the USDC amount paid is under makerAmountFilled. On the contrary, takerAssetId = 0 indicates a sell, and the USDC amount the user receives is under takerAmountFilled.CASEWHEN makerAssetId = 0 THEN 'Buy shares negrisk'WHEN takerAssetId = 0 THEN 'Sell shares negrisk'END AS "Buy/Sell" -- I use this case because if you check transaction logs, and OrdersMatched event, both buy and sell shares have similar arguments. However, I noticed that when 'buy' the makerAssetId = 0, and when 'sell' takerAssetId = 0FROMpolymarket_polygon.NegRiskCtfExchange_evt_OrdersMatched),buysell_ctf AS (SELECTtakerOrderMaker AS user_address,CASEWHEN makerAssetId = 0 THEN makerAmountFilled / 1e6WHEN takerAssetId = 0 THEN takerAmountFilled / 1e6END AS "Amount USDC",CASEWHEN makerAssetId = 0 THEN 'Buy shares ctf'WHEN takerAssetId = 0 THEN 'Sell shares ctf'END AS "Buy/Sell"FROMpolymarket_polygon.CTFExchange_evt_OrdersMatched),Aggregated_negrisk AS (SELECTuser_address,SUM(CASE WHEN "Buy/Sell" = 'Buy shares negrisk' THEN "Amount USDC" ELSE 0 END) AS total_buy_negrisk,SUM(CASE WHEN "Buy/Sell" = 'Sell shares negrisk' THEN "Amount USDC" ELSE 0 END) AS total_sell_negriskFROMBuySell_negriskGROUP BYuser_address),Aggregated_ctf AS (SELECTuser_address,SUM(CASE WHEN "Buy/Sell" = 'Buy shares ctf' THEN "Amount USDC" ELSE 0 END) AS total_buy_ctf,SUM(CASE WHEN "Buy/Sell" = 'Sell shares ctf' THEN "Amount USDC" ELSE 0 END) AS total_sell_ctfFROMbuysell_ctfGROUP BYuser_address),redeem_ctf AS (SELECTredeemer AS user_address,SUM(payout / 1e6) AS redeem_payout_ctfFROM polymarket_polygon.ctf_evt_PayoutRedemptionWHERE evt_block_time >= DATE '2024-01-01'GROUP BY redeemer),