Lesson 14.4

SQL Practice: Supplier Margin Report

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

Revenue alone doesn't 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 cost estimate uses the product table's current cost for every past sale, because Garden Shop doesn't record what an item cost when it sold.

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 Shop Tables · suppliers, products, order_items, orders 4 tables

Table · suppliers

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

4 columns · 6 rows

supplier_id int supplier_name text contact_email text state text

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Build a Top-5 supplier margin report for shipped orders. Return supplier_name, revenue, cost, gross_margin, and margin_pct. Round money to two decimals and margin_pct to one decimal. Rank by gross_margin descending, then supplier_name, and keep the top five.