SET @StartDate = '2010-01-01';
SET @EndDate   = '2025-12-05';

WITH per_order AS (
  SELECT
      o.orderid,
      o.salesrep_customerid,
      COALESCE(o.coupon_discount, 0)       AS coupon_discount,
      SUM(od.totalprice)                   AS order_sales,
      SUM(od.quantity)                     AS order_qty,
      SUM(od.quantity * od.vendor_price)   AS order_cogs,
      COUNT(od.orderdetailid)              AS orderdetails
  FROM orders o
  JOIN orderdetails od
    ON od.orderid = o.orderid
  WHERE DATE(o.orderdate) BETWEEN DATE(@StartDate) AND DATE(@EndDate)
    AND o.salesrep_customerid IS NOT NULL
    AND o.orderstatus <> 'Cancelled'
    AND od.productcode NOT LIKE 'DSC-%'
    AND od.productcode NOT LIKE '%(C)%'
    AND od.productcode NOT LIKE '%(R)%'
    AND o.salesrep_customerid NOT IN ('267969','365719','628918','765611','865308')
  GROUP BY
      o.orderid,
      o.salesrep_customerid,
      COALESCE(o.coupon_discount, 0)
)

SELECT
  CASE
    WHEN salesrep_customerid = '561739'  THEN 'Mick'
    WHEN salesrep_customerid = '2554513' THEN 'Josie'
    WHEN salesrep_customerid = '480482'  THEN 'Marta'
    WHEN salesrep_customerid = '879320'  THEN 'Ron'
    WHEN salesrep_customerid = '2451660' THEN 'Tonya'
    WHEN salesrep_customerid = '2451667' THEN 'Michelle'
    WHEN salesrep_customerid = '4225668' THEN 'Destiny'
    WHEN salesrep_customerid = '5000002' THEN 'Coral'
    WHEN salesrep_customerid = '2203115' THEN 'Ray'
    WHEN salesrep_customerid = '2652776' THEN 'Danah'
    WHEN salesrep_customerid = '2763216' THEN 'Monique'
    WHEN salesrep_customerid = '1915168' THEN 'Randolf'
    WHEN salesrep_customerid = '3117788' THEN 'Tovi'
    WHEN salesrep_customerid = '3117859' THEN 'Nina'
    WHEN salesrep_customerid = '3535982' THEN 'Kim'
    WHEN salesrep_customerid = '3200319' THEN 'Katherine'
    WHEN salesrep_customerid = '4052221' THEN 'Glenn'
    WHEN salesrep_customerid = '4052206' THEN 'Kimberly'
    WHEN salesrep_customerid = '4142564' THEN 'Jeff'
    WHEN salesrep_customerid = '5000000' THEN 'Paolo'
    WHEN salesrep_customerid = '339339'  THEN 'Royce'
    WHEN salesrep_customerid = '5000007' THEN 'Kylie'
    WHEN salesrep_customerid = '5000008' THEN 'Freya'
    WHEN salesrep_customerid = '5000003' THEN 'Glenn F'
    WHEN salesrep_customerid = '5000006' THEN 'VPW_Computer'
    ELSE salesrep_customerid
  END AS SalesRep,
  COUNT(*)                              AS o,                 -- orders
  SUM(orderdetails)                     AS OrderDetails,
  SUM(order_sales)                      AS Sales,
  SUM(order_qty)                        AS Quantity,
  SUM(order_cogs)                       AS COGS,
  (SUM(order_sales) - SUM(coupon_discount) - SUM(order_cogs)) AS PROFIT,
  /* profit margin = profit / (cogs + profit) = profit / net_revenue */
  COALESCE(
    (SUM(order_sales) - SUM(coupon_discount) - SUM(order_cogs))
    / NULLIF(SUM(order_cogs) + (SUM(order_sales) - SUM(coupon_discount) - SUM(order_cogs)), 0),
    0
  ) AS Profit_Margin
FROM per_order
GROUP BY SalesRep
ORDER BY o DESC;