파트너 정산 마진 가시화로 채널별 수익성 분석 가능

SELECT
  c.name as coupon_name,
  c.sale_price,
  cs.supply_price,
  c.sale_price - cs.supply_price as gross_margin,
  ROUND((c.sale_price - cs.supply_price) / c.sale_price * 100, 2) as margin_rate,
  SUM(ch.quantity) as sold_qty,
  SUM(ch.quantity) * (c.sale_price - cs.supply_price) as total_margin
FROM 내부테이블 c
JOIN 내부테이블 cs ON cs.coupon_id = c.id
LEFT JOIN 내부테이블 ch ON ch.coupon_id = c.id AND ch.status = 'USED'
GROUP BY c.id
ORDER BY margin_rate DESC;