Restaurant Sales Report
Design a small PostgreSQL database for a restaurant and write the reports its owner would ask for: how much money came in each day, which dishes sell the most, and how much the average order is worth. You'll create tables for menu categories, menu items, orders and the items in each order, fill them with a few weeks of realistic sample orders, and write the queries, run from psql. Prices are copied into each order line, so changing the menu later doesn't change past sales. It's a friendly introduction to SQL: you'll learn creating tables with keys and constraints, inserting data, joining tables, grouping and totals with GROUP BY, and saving reports as views.
- Estimate
- ~8h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
psql · PL/pgSQL
Project roadmap
- 01
Define what the reports show
~1hWrite README.md describing the three reports: daily revenue (date, number of orders, total), the 10 best-selling items (name, quantity sold, revenue), and the average order value per day of the week. Explain why each order line stores the price at the time of the order. Install PostgreSQL and create a database for the project. Done when: README.md describes every report and its columns.
- 02
Create the tables
~1.5hWrite schema.sql with categories, menu_items (name, category, price as numeric(8,2), available), orders (id, created at, table number, status open, paid or cancelled) and order_items (order, menu item, quantity, unit price). Add primary keys, foreign keys and checks: prices greater than 0, quantity at least 1, and a status from the allowed list. Done when: psql -f schema.sql creates every table without errors.
- 03
Add sample data
~1.5hWrite seed.sql with about 5 categories and 25 menu items, then generate 3 weeks of orders with generate_series and random(), with more orders on weekends, 1 to 5 items per order, and a few cancelled orders. Copy the menu price into each order line. Done when: the data loads, and simple counts show orders on every day.
- 04
Write the reports
~2hWrite reports.sql with the three reports using JOIN, SUM, COUNT, AVG and GROUP BY, counting only paid orders. Round money to 2 decimals and sort the results sensibly. Save each report as a view (daily_revenue, top_items, avg_order_by_weekday). Done when: each view returns sensible numbers for the sample data.
- 05
Handle edge cases
~1hMake daily revenue show days with no orders as 0, using generate_series and a LEFT JOIN. Check that cancelled orders never count, that a menu price change doesn't change past revenue, and that an order with no items doesn't break the average. Done when: every one of these cases gives the right result.
- 06
Test the reports
~1hWrite test.sql that, in a transaction, inserts a few orders where you know the exact answers, compares the views' results with them (raising an error with a PL/pgSQL DO block if they differ), 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