Inventory Reorder Report
Design a small PostgreSQL database for a shop's warehouse and write the reports that tell staff what to reorder: which products have fallen below their reorder level, how many to order to get back to the target, which supplier to order from, and how much the current stock is worth in total, per category and per supplier. You'll create the tables, fill them with sample data, and write the queries in psql, saving the useful ones as views. It's a friendly introduction to SQL: you'll learn creating tables with keys and constraints, joining tables, filtering, calculated columns, grouping and totals, and handling missing data.
- Estimate
- ~7h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · psql
Project roadmap
- 01
Define the reports
~1hWrite README.md describing the reports: products to reorder (product, current stock, reorder level, quantity to order to reach the target level, supplier and estimated cost), stock value per category and per supplier (quantity × unit cost), and products with no supplier assigned. Install PostgreSQL and create a database. Done when: README.md defines each report and its columns.
- 02
Create the tables
~1.5hWrite schema.sql with suppliers (name, email, lead time in days), categories, and products (SKU unique, name, category, supplier which may be empty, quantity in stock, reorder level, target level, unit cost as numeric(10,2) and an active flag). Add checks: quantities and costs not negative, and the target level above the reorder level. Done when: psql -f schema.sql runs without errors.
- 03
Add sample data
~1hWrite seed.sql with about 5 suppliers, 6 categories and 60 products: some well stocked, some below their reorder level, some at exactly the reorder level, some with no supplier, and some inactive. Done when: the data loads, and each case is present.
- 04
Write the reorder report
~1.5hWrite a query listing active products at or below their reorder level with the quantity to order (target minus stock), the supplier's name and lead time, and the estimated cost, sorted by supplier then product. Use a LEFT JOIN so products without a supplier still appear (as No supplier). Save it as a view reorder_list. Done when: the view lists exactly the products that need reordering.
- 05
Write the stock value reports
~1hWrite queries for the total stock value per category and per supplier with GROUP BY, including the number of products and the share of the total value, rounded to 2 decimals, plus a grand total. Save them as views. Done when: the totals add up to the grand total.
- 06
Test the reports
~1hWrite test.sql that, in a transaction, changes a few stock quantities with known effects (a product dropping below its level, one being restocked) and checks the views' results, raising an error with a DO block if they don't match, then rolls back. Done when: psql -f test.sql runs without errors.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack