Modify Customer Order Data with SQL in PostgreSQL

Table of Contents

1. Challenge

   Step 1: Reconcile inventory and shipments

   In this step, you will explore the warehouse inventory and shipment data used throughout the lab.

   Operations analysts at a fictional distribution company have discovered inconsistencies between warehouse inventory records and outbound shipment reports. Before investigating those discrepancies, you need to understand the available data and identify how records relate across tables.

   You will inspect the inventory and shipment datasets, identify the columns used to connect records, and run a starter query that previews inventory items with shipment activity.

   By the end of this step, you will understand:

   • Which tables store inventory and shipment information.
   • Which columns are used to connect records across tables.
   • How inventory and shipment records align before reconciliation begins.
2. Challenge

   Step 2: Match Inventory and Shipments with Joins

   In this step, you will use SQL joins to compare warehouse inventory records with outbound shipment records.

   The operations team needs to know which inventory items already have shipment activity, which inventory records do not have shipments yet, and which records exist on only one side of the reconciliation. Different join types answer different reconciliation questions.

   You will use INNER JOIN, LEFT JOIN, and FULL OUTER JOIN to compare the same two operational tables from different perspectives.

   By the end of this step, you will understand:

   • How INNER JOIN returns only records that match across both tables.
   • How LEFT JOIN keeps inventory records even when shipment records are missing.
   • How FULL OUTER JOIN surfaces unmatched records from either table.
3. Challenge

   Step 3: Flag Missing, Delayed, and Mismatched Shipments

   In this step, you will add comparison logic to identify shipment exceptions.

   After inventory and shipment records are joined, analysts need to understand where the data does not line up. Some shipments may have different expected and actual quantities. Others may have shipped later than planned. Some records may need a clear status label so the operations team can decide what to review first.

   You will compare shipment quantities, evaluate shipment dates, and create a reconciliation status that classifies each shipment record.

   By the end of this step, you will understand:

   • How to compare expected and actual shipment quantities.
   • How to identify delayed shipments using planned and actual dates.
   • How to use CASE logic to label reconciliation outcomes.
4. Challenge

   Step 4: Compare Regional Reports with Set Operations

   In this step, you will use SQL set operations to compare regional shipment reports.

   The operations team receives shipment report extracts from multiple regions. Sometimes the same shipment appears in more than one regional report, and sometimes a shipment appears in one report but not another. Set operations help analysts compare these result sets directly.

   You will use UNION, INTERSECT, and EXCEPT to combine, compare, and isolate shipment records across regional reports.

   By the end of this step, you will understand:

   • How UNION combines rows from multiple query results.
   • How INTERSECT returns rows that appear in both query results.
   • How EXCEPT returns rows from one query result that do not appear in another.
5. Challenge

   Step 5: Organize Reconciliation Logic with a WITH CTE

   In this step, you will organize reconciliation logic with Common Table Expressions, or CTEs.

   As reconciliation queries grow, they can become difficult to read and maintain. A CTE helps you break a larger query into named sections so each part of the logic has a clear purpose. This is useful when you need to join data, calculate exception statuses, and filter final results in one workflow.

   You will create a base reconciliation dataset, extend it with status labels, and return only the records that require analyst review.

   By the end of this step, you will understand:

   • How to define a query section with WITH.
   • How to use one CTE as the foundation for another query step.
   • How to make reconciliation logic easier to read, test, and explain.
6. Challenge

   Step 6: Validate the Final Inventory Reconciliation

   In this step, you will validate the final reconciliation results and produce an analyst-ready report.

   After building joins, exception logic, set comparisons, and CTEs, the final step is to confirm that the reconciliation output is trustworthy. Analysts need to verify that matched and unmatched records are counted correctly, exception categories are classified correctly, and the final report includes the fields needed for review.

   You will run validation queries that summarize reconciliation outcomes, then create a clean final report that combines inventory and shipment data.

   By the end of this step, you will understand:

   • How to validate matched and unmatched reconciliation records.
   • How to confirm delayed and mismatched shipment classifications.
   • How to produce a final report that analysts can use to investigate inventory and shipment discrepancies.