Supplier Inventory: Building the Dashboard • fakhrimhd

This is Part 3 of the Supplier Inventory series.

The Questions

With the database populated, what can we actually learn from it?

Which suppliers deliver on time?
Where is the spend concentrated?
What inventory needs attention?
Which orders are overdue?

This post walks through 7 analytical queries that answer these questions using PostgreSQL-specific features.

The Approach

I wrote 7 analytical queries in analysis.sql that answer real supply chain questions.

Each query showcases a PostgreSQL-specific technique:

#	Query	Technique
1	Supplier On-Time Delivery Rate	`FILTER` clause
2	Lead Time Analysis	`PERCENTILE_CONT` for median
3	Contract Spend by Supplier	CTE + window functions
4	Inventory Status with Alerts	`CASE` threshold flags
5	Material Price Benchmarking	Named `WINDOW` clause
6	Monthly Order Volume Trend	`DATE_TRUNC` time series
7	Pending Orders Risk Report	CTE + aggregation

Query Highlights

1. Supplier On-Time Delivery Rate

Question: Which suppliers deliver on time?

SELECT
    s.company_name AS supplier,
    COUNT(o.order_id) AS total_orders,
    COUNT(*) FILTER (WHERE o.shipped_date <= o.required_date) AS on_time,
    COUNT(*) FILTER (WHERE o.shipped_date > o.required_date) AS late,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE o.shipped_date <= o.required_date)
        / NULLIF(COUNT(o.order_id), 0), 1
    ) AS on_time_rate_pct
FROM orders o
JOIN contract c ON o.contract_id = c.contract_id
JOIN supplier s ON c.supplier_id = s.supplier_id
WHERE o.shipped_date IS NOT NULL
GROUP BY s.supplier_id, s.company_name
ORDER BY on_time_rate_pct DESC;

PostgreSQL feature: FILTER clause — cleaner than CASE WHEN for conditional aggregation.

2. Lead Time Analysis

Question: How long does each supplier take to deliver?

SELECT
    s.company_name AS supplier,
    m.material_name AS material,
    ROUND(AVG(o.shipped_date - o.order_date), 1) AS avg_lead_time_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY (o.shipped_date - o.order_date)
    ) AS median_lead_time_days,
    MIN(o.shipped_date - o.order_date) AS min_lead_time_days,
    MAX(o.shipped_date - o.order_date) AS max_lead_time_days
FROM orders o
JOIN contract c ON o.contract_id = c.contract_id
JOIN supplier s ON c.supplier_id = s.supplier_id
JOIN material m ON c.material_id = m.material_id
WHERE o.shipped_date IS NOT NULL
GROUP BY s.supplier_id, s.company_name, m.material_id, m.material_name;

PostgreSQL feature: PERCENTILE_CONT — calculates true median, not available in MySQL.

3. Contract Spend by Supplier

Question: Where is the money going? Who are the top suppliers?

WITH supplier_spend AS (
    SELECT
        s.company_name AS supplier,
        COUNT(c.contract_id) AS total_contracts,
        SUM(c.contract_value) AS total_spend
    FROM contract c
    JOIN supplier s ON c.supplier_id = s.supplier_id
    GROUP BY s.supplier_id, s.company_name
)
SELECT
    supplier,
    total_contracts,
    total_spend,
    ROUND(
        100.0 * total_spend / SUM(total_spend) OVER (), 1
    ) AS pct_of_total_spend,
    ROUND(
        100.0 * SUM(total_spend) OVER (ORDER BY total_spend DESC)
        / SUM(total_spend) OVER (), 1
    ) AS cumulative_spend_pct
FROM supplier_spend
ORDER BY total_spend DESC;

PostgreSQL feature: Window functions (OVER()) for running totals and percentages.

4. Inventory Status with Alerts

Question: What stock levels need attention?

SELECT
    pu.unit_name AS production_unit,
    m.material_name AS material,
    ui.quantity AS current_stock,
    CASE
        WHEN ui.quantity < 20  THEN 'CRITICAL'
        WHEN ui.quantity < 50  THEN 'LOW'
        WHEN ui.quantity < 100 THEN 'ADEQUATE'
        ELSE 'SUFFICIENT'
    END AS stock_status
FROM unit_inventory ui
JOIN production_unit pu ON ui.unit_id = pu.unit_id
JOIN material m ON ui.material_id = m.material_id
ORDER BY
    CASE WHEN ui.quantity < 20 THEN 1
         WHEN ui.quantity < 50 THEN 2
         WHEN ui.quantity < 100 THEN 3
         ELSE 4 END,
    ui.quantity;

Use case: Red/yellow/green dashboard cards for inventory health.

5. Material Price Benchmarking

Question: Are we paying fair prices? Who’s cheapest?

SELECT
    m.material_name AS material,
    s.company_name AS supplier,
    c.price_per_unit,
    MIN(c.price_per_unit) OVER w AS lowest_price,
    MAX(c.price_per_unit) OVER w AS highest_price,
    ROUND(
        100.0 * (c.price_per_unit - MIN(c.price_per_unit) OVER w)
        / NULLIF(MIN(c.price_per_unit) OVER w, 0), 1
    ) AS pct_above_cheapest
FROM contract c
JOIN material m ON c.material_id = m.material_id
JOIN supplier s ON c.supplier_id = s.supplier_id
WINDOW w AS (PARTITION BY m.material_id)
ORDER BY m.material_name, c.price_per_unit;

PostgreSQL feature: Named WINDOW clause — define once, reuse multiple times.

6. Monthly Order Volume Trend

Question: How does ordering volume change over time?

SELECT
    DATE_TRUNC('month', o.order_date)::date AS order_month,
    COUNT(o.order_id) AS total_orders,
    COUNT(*) FILTER (WHERE o.status = 'Completed') AS completed,
    COUNT(*) FILTER (WHERE o.status = 'Pending') AS pending,
    COUNT(DISTINCT c.supplier_id) AS active_suppliers
FROM orders o
JOIN contract c ON o.contract_id = c.contract_id
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY order_month;

PostgreSQL feature: DATE_TRUNC — proper time-series grouping (not string manipulation).

7. Pending Orders Risk Report

Question: What orders are overdue? What’s the exposure?

WITH overdue AS (
    SELECT
        o.order_id,
        s.company_name AS supplier,
        m.material_name AS material,
        o.required_date,
        (CURRENT_DATE - o.required_date) AS days_overdue,
        c.contract_value AS value_at_risk
    FROM orders o
    JOIN contract c ON o.contract_id = c.contract_id
    JOIN supplier s ON c.supplier_id = s.supplier_id
    JOIN material m ON c.material_id = m.material_id
    WHERE o.status = 'Pending'
      AND o.required_date < CURRENT_DATE
)
SELECT *, SUM(value_at_risk) OVER () AS total_value_at_risk
FROM overdue
ORDER BY days_overdue DESC;

Use case: Daily risk dashboard — “We have $X at risk from Y overdue orders.”

What I’d Do Differently

Add reorder_point to unit_inventory — Would make Query #4 dynamic instead of hardcoded thresholds
Create materialized views — For dashboards hitting large tables, precompute aggregations
Add date dimension table — Would simplify time-series queries and enable fiscal calendar support

Previous: ← Building the Interface

Back to: Overview

fakhrimhd/supplier-inventory

Lorem ipsum dolor sit amet, consectetur adipiscing elit.

00K00KMIT