Analiza Sprzedaży i Rentowności Cocktail Baru

WITH sales_per_cocktail AS (
  SELECT
    product_name,
    ROUND(AVG(product_price_gross / (1 + tax_rate)), 2) AS selling_price_net
  FROM `clean.sales`
  WHERE product_price_gross IS NOT NULL
  GROUP BY 1
)
SELECT
  b.cocktail_name,
  s.selling_price_net,
  b.beverage_cost AS beverage_cost_net,
  ROUND(s.selling_price_net - b.beverage_cost, 2) AS net_margin_pln,
  ROUND(b.beverage_cost / s.selling_price_net, 3) AS beverage_cost_pct,
  ROUND(1 - (b.beverage_cost / s.selling_price_net), 3) AS net_margin_pct
FROM `clean.cocktail_beverage_costs` b
LEFT JOIN sales_per_cocktail s ON b.cocktail_name = s.product_name
ORDER BY beverage_cost_pct DESC