Capstone Project and PostgreSQL Interview Preparation

Design a practical schema, implement common queries, validate performance, and review core interview concepts.

Capstone: Design a Course Platform Database

Build a small course platform schema with users, courses, enrollments, lessons, and progress. Define primary and foreign keys, constraints, useful timestamps, and indexes around expected access patterns. Think in terms of entities and relationships first, then write queries that answer product questions such as most-enrolled courses or a user's course progress.

A useful capstone includes schema creation, seed data, read queries, updates inside transactions, a migration, and a performance review using EXPLAIN. The goal is not merely to make SQL run; it is to make the data model enforce the domain's important invariants.

Core capstone schema

CREATE TABLE courses (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE enrollments (
  user_id BIGINT NOT NULL REFERENCES users(id),
  course_id BIGINT NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, course_id)
);

CREATE INDEX idx_enrollments_course
ON enrollments (course_id);

💡 This excerpt assumes a users table already exists. The composite primary key prevents duplicate enrollments, while the course index can help course-centric lookups and counts.

Key Takeaways

Model entities and relationships before writing queries.
Use constraints to encode important invariants.
Index based on expected access patterns and verify with plans.

Knowledge Check

Which key prevents the same user from enrolling in the same course more than once?

Capstone: Reporting Queries

Write queries that aggregate enrollments, calculate course popularity, and show a user's progress. Ensure counts reflect the intended grain and that outer joins preserve courses with zero enrollments when needed. Add deterministic ordering and pagination for user-facing lists.

Test queries with edge cases: empty tables, missing relationships, duplicate-looking values, NULLs, and large result sets. A query that works for one sample row may still be logically wrong or slow at scale.

Courses including zero enrollments

SELECT c.id, c.title, COUNT(e.user_id) AS enrollment_count
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY enrollment_count DESC, c.id
LIMIT 20;

💡 The LEFT JOIN keeps courses with no enrollments. COUNT(e.user_id) returns zero for unmatched courses because the joined user_id is NULL.

Key Takeaways

Use LEFT JOIN when zero-related-row entities must remain in results.
COUNT(column) excludes NULL, allowing zero counts for unmatched rows.
Test empty and edge-case data, not only happy paths.

Knowledge Check

Why use COUNT(e.user_id) instead of COUNT(*) in this LEFT JOIN aggregation?

Capstone: Performance and Production Checklist

Measure the query with representative data and inspect its plan. Consider indexes on foreign keys and filters, but avoid adding indexes without evidence. Confirm that the application uses bound parameters, a bounded connection pool, transaction handling, and least-privilege roles. Ensure backups can be restored and schema migrations can be deployed safely.

A production-ready database is not just a schema. It also requires monitoring, access control, backup and recovery procedures, capacity planning, and a clear operational owner. Document assumptions so future developers can safely change the model.

Performance review commands

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.title, COUNT(e.user_id) AS enrollment_count
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY enrollment_count DESC, c.id
LIMIT 20;

💡 Run EXPLAIN ANALYZE in an appropriate environment because it executes the query. Compare actual rows and timings, and repeat tests with realistic table sizes and statistics.

Key Takeaways

Performance work should start with measurement.
Production readiness includes security, backups, monitoring, and migrations.
Document operational assumptions and recovery procedures.

Knowledge Check

What is the best first step when a query is slow?

Capstone: Design a Course Platform Database

Capstone: Reporting Queries

Capstone: Performance and Production Checklist

STAY UPDATED WITH BYTES!