Which SKUs sell on both Amazon and Shopify — and where do they perform better?

WITH amazon_skus AS (
  SELECT
    i.sku,
    SUM(i.item_price * i.quantity)    AS amazon_revenue,
    SUM(i.quantity)                   AS amazon_units,
    COUNT(DISTINCT o.amazon_order_id) AS amazon_orders
  FROM amazon_order_items i
  JOIN amazon_orders o ON o.amazon_order_id = i.amazon_order_id
  WHERE o.order_status NOT IN ('Canceled', 'Cancelled')
    AND i.item_price IS NOT NULL
    AND o.purchase_date::date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY i.sku
),
shopify_skus AS (
  SELECT
    li.sku,
    SUM(li.price * li.quantity)       AS shopify_revenue,
    SUM(li.quantity)                  AS shopify_units,
    COUNT(DISTINCT li.order_id)       AS shopify_orders
  FROM shopify_order_line_items li
  JOIN shopify_orders o ON o.id = li.order_id
  WHERE o.financial_status IN ('paid', 'partially_refunded')
    AND o.cancelled_at IS NULL
    AND o.created_at >= NOW() - INTERVAL '90 days'
  GROUP BY li.sku
)
SELECT
  COALESCE(a.sku, s.sku)             AS sku,
  ROUND(COALESCE(a.amazon_revenue, 0)::numeric, 2) AS amazon_revenue,
  COALESCE(a.amazon_units, 0)        AS amazon_units,
  ROUND(COALESCE(s.shopify_revenue, 0)::numeric, 2) AS shopify_revenue,
  COALESCE(s.shopify_units, 0)       AS shopify_units,
  ROUND((COALESCE(a.amazon_revenue, 0) + COALESCE(s.shopify_revenue, 0))::numeric, 2) AS combined_revenue,
  CASE
    WHEN a.sku IS NOT NULL AND s.sku IS NOT NULL THEN 'Both'
    WHEN a.sku IS NOT NULL THEN 'Amazon only'
    ELSE 'Shopify only'
  END                                AS channel_status
FROM amazon_skus a
FULL OUTER JOIN shopify_skus s ON s.sku = a.sku
ORDER BY combined_revenue DESC
LIMIT 50