Sales reports for a shop

The problem

Build the database for a small shop's products, customers and orders, and write the reports the owner needs to run the business.

Requirements

• Tables for customers, products, orders and order lines, with keys and constraints
• A report of each month's sales, with the number of orders and the total
• A report of the best-selling products and of products that have not sold at all
• A list of each customer's total spending, including customers who have bought nothing
• A query that labels customers as new, regular or loyal with CASE
• Indexes chosen for the reports, with their query plans checked

Technologies

• SQL
• SQLite
• Joins
• GROUP BY
• Subqueries
• Indexes

Step by step

1. Design the four tables and the relationships between them.
2. Insert sample data covering several months, customers and products.
3. Write the monthly report with GROUP BY on the month.
4. Write the product reports, using LEFT JOIN for products that have not sold.
5. Write the customer reports, with COALESCE for customers with no orders.
6. Label customers with CASE, using a subquery for each customer's order count.
7. Add indexes, and compare the query plans before and after.

Expected result

A shop database and a set of reports that answer the owner's questions correctly.

How it is evaluated

• Every report is correct, including customers and products with no sales
• Totals are worked out by the database, not by hand
• Queries are readable, with clear aliases and WITH steps where they help
• Each index is justified by a query that uses it

Ways to improve it

• Add discounts and show their effect on totals
• Compare each month with the same month a year earlier
• Turn the reports into views