SQL Investigation & Performance

Build a billing dataset with realistic anomalies, then write the queries to find duplicate charges and stuck orders — and make a slow report fast with an index.

Data bugs hide in plain sight. Here we build a small billing schema, seed it with the kinds of anomalies real systems accumulate — a double charge, an order stuck between states — and then write the investigation queries that surface them. We finish on performance: read a query plan, see the full scan behind a slow report, and add the index that fixes it. Each step is one SQL file with a short note on what we're looking for.

What you'll build

• Model a small billing schema (users, orders, payments)
• Seed realistic anomalies on purpose
• Write queries that find duplicate charges and stuck orders
• Read an EXPLAIN plan and fix a slow query with an index

Contents

1. A database to investigate
2. Users
3. Orders
4. Payments
5. Seed some users
6. Seed some orders
7. Seed the anomalies
8. Find the double charges
9. Find the stuck orders
10. A reporting query
11. Read the plan
12. Fix it with an index