Lesson 13.4

SQL Practice: Supplier Margin Report

Close the operations chapter by ranking suppliers on shipped gross margin.

Revenue alone does not tell the whole supplier story. A supplier can sell a lot of product but produce a thinner margin if costs are high. This report compares shipped revenue, estimated cost, gross margin, and margin rate.

The query walks across four tables: suppliers to products, products to line items, and line items to orders so only shipped sales count.

Margin is a line-item calculation

Calculate margin before summing: quantity * (unit_price - cost). Then divide total margin by total revenue for the margin percentage.

Pattern

select supplier_name,
       round(sum(quantity * sale_price), 2) as revenue,
       round(sum(quantity * unit_cost), 2) as cost,
       round(sum(quantity * (sale_price - unit_cost)), 2) as gross_margin,
       round(100.0 * sum(quantity * (sale_price - unit_cost)) / sum(quantity * sale_price), 1) as margin_pct
from joined_sales
group by supplier_name
order by gross_margin desc
limit 5

Schema · Garden ShopTable · suppliers4 columns · 6 rows

Table · suppliers

One row per supplier. Some suppliers have no contact email.

4 columns · 6 rows

supplier_id intsupplier_name textcontact_email textstate text

Your task

Build a Top-5 supplier margin report for shipped orders. Returnsupplier_name, revenue,cost, gross_margin, andmargin_pct. Round money to two decimals and margin_pct to one decimal.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer