Get Trades Stats by Wallet & Marketplace

WITH
  buy_side AS (
    SELECT
      buyer AS buyer_address,
      platform,
      COUNT(*) AS total_purchase_count,
      SUM(usd_amount) AS total_spending_usd
    FROM
      nft.ethereum_trades
    GROUP BY
      buyer,
      platform
  ),
  sell_side AS (
    SELECT
      seller AS seller_address,
      platform,
      COUNT(*) AS total_sales_count,
      SUM(usd_amount) AS total_sales_usd
    FROM
      nft.ethereum_trades
    GROUP BY
      seller,
      platform
  ),
  end_table AS (
    SELECT
      COALESCE(buy_side.buyer_address, sell_side.seller_address) AS wallet_address,
      COALESCE(buy_side.platform, sell_side.platform) AS platform,
      buy_side.total_purchase_count AS total_purchase_count,
      buy_side.total_spending_usd AS total_spending_usd,
      sell_side.total_sales_count AS total_sales_count,
      sell_side.total_sales_usd AS total_sales_usd
    FROM
      buy_side FULL
      JOIN sell_side ON buy_side.buyer_address = sell_side.seller_address
      AND buy_side.platform = sell_side.platform
  )
SELECT * FROM end_table;