After this lesson, you will be able to: Design + build a realistic database (8+ tables, real relationships) and ship a query gallery as a portfolio piece.
Schema + queries are what data-engineer interviews actually test. A polished ER diagram + 15 well-written queries on a real domain proves it better than any cert.
Real-world data with real questions.
Option A: SaaS subscription billing, users, subscriptions, plans, payments, invoices, refunds.
Option B: Library system, books, authors, members, loans, holds, fines.
Option C: E-commerce, products, categories, customers, orders, line items, reviews, inventory.
Option D: Movie database, movies, actors, directors, genres, ratings, watch history.
Same for any domain.
1. ER diagram (dbdiagram.io export, in README).
2. SQL schema with 8+ tables, FKs, NOT NULL, CHECK constraints, sensible types.
3. Seed script, `generate_series` or real CSV import to ~10k rows total.
4. Query gallery: 15 queries in a single file, each commented with the business question.
5. Indexes: at least 5 thoughtful ones (NOT autocreated from PKs); explain why in README.
6. README with: ER diagram screenshot, design decisions, query gallery TOC, EXPLAIN ANALYZE on 2-3 queries.
7. Hosted on Neon free tier so anyone can connect + read.
Mix easy + hard to show range.
1-3: Basic SELECT + WHERE + ORDER BY.
4-6: JOINs (INNER + LEFT) across 3+ tables.
7-9: GROUP BY + HAVING with aggregations.
10-11: CTEs (multi-stage logic).
12-13: Window functions (top-N per group, running totals).
14: Recursive CTE (if your domain has hierarchy).
15: A genuine perf-optimized query (with EXPLAIN before + after index).
Neon free tier (neon.tech). Create a project, run the schema + seed, share a read-only connection string in the README. Bonus: a small Node.js or Python script that runs the query gallery on demand, hosted on Vercel as a tiny page.
Sign in and purchase access to unlock this lesson.