Assignments
Each assignment listed with its scope, what was learned, and the link to the deliverable. Updated as the term moves.
Your First Cloud Database
Provision a cloud-hosted PostgreSQL instance and execute a "Status Check" query to verify connectivity.
Submission Links
What I Learned
- How to provision a PostgreSQL instance on Render
- Configuring connection strings and environment variables
- Basic SQL queries for database health checks
- Setting up a database schema from scratch
Key Takeaways
The importance of connection pooling and how cloud databases differ from local development instances. Security considerations for exposing database endpoints.
Mapping Real-World Data to Postgres Types
You are building a Community Garden Management System. You need to create a table called plants. Look at the following data points and determine the best PostgreSQL type for each.
Submission Links
What I Learned
- PostgreSQL data types vs. real-world data modeling
- Choosing between VARCHAR, TEXT, and CHAR
- When to use ENUM types vs. check constraints
- Handling monetary values with NUMERIC vs. MONEY
Key Takeaways
Data type selection has performance implications. VARCHAR(255) isn't always the right choice—consider actual data constraints and future growth.
Building "StreamFlix" & The Big Data Threshold
You have been hired as the lead Data Engineer for StreamFlix. Your first task is to build a schema that can track movies, handle a sudden rebranding of your data columns, and demonstrate how you would manage a massive influx of temporary "watch-session" data.
Submission Links
What I Learned
- Designing scalable database schemas for streaming platforms
- Handling schema migrations and rebranding scenarios
- Managing temporary high-volume data with partitioned tables
- Implementing appropriate indexing strategies
Key Takeaways
Schema design must accommodate future changes. Temporary tables and partitioning are essential for handling burst data loads in streaming services.
Building the Referential Bridge
Your "StreamFlix" app is growing. You now need to allow users to rate movies. However, if you delete a movie, you don't want "ghost ratings" (ratings for a movie that no longer exists) cluttering your database. You will implement a Foreign Key to prevent this.
Submission Links
What I Learned
- Implementing foreign key constraints for data integrity
- CASCADE vs. RESTRICT vs. SET NULL deletion policies
- Designing normalized schemas for user-generated content
- Performance implications of referential integrity
Key Takeaways
Foreign keys aren't just about relationships—they're crucial for maintaining data consistency. Choosing the right ON DELETE action depends on business requirements.
Supabase Performance Tuning
In the previous assignment, you built a movies and ratings schema. Now, your dataset has grown to 1,000,000 ratings, and the "Top Rated Movies" dashboard is loading slowly.
Submission Links
Current Progress
- Analyzed query execution plans with EXPLAIN ANALYZE
- Identified full table scans as the main performance bottleneck
- Implemented composite indexes on frequently queried columns
- Working on query optimization and materialized views
Expected Learnings
Query optimization techniques, index selection strategies, and performance tuning for large datasets using Supabase.
The "StreamFlix Extra Features" Challenge
In the previous weeks, we treated movies as rigid rows. But in the real world, a documentary has different data points (like "Interviewees") than an animated film (like "Voice Cast") or a 4K blockbuster (like "HDR format"). We're going to use JSONB to turn our movies table into a hybrid powerhouse.
Submission Links
What I Learned
- JSONB data type in PostgreSQL for flexible schema design
- Creating GIN indexes on JSONB columns for query performance
- Querying nested JSON data with operators like ->, ->>, and @>
- Balancing structured columns with semi-structured JSONB fields
Key Takeaways
JSONB provides flexibility for domain-specific metadata without sacrificing query performance when properly indexed. It's a powerful middle ground between rigid schemas and fully unstructured data.
StreamFlix "Curation and Clean-up" Challenge
Write precise PostgreSQL queries to filter a movie catalog based on specific dates, text patterns, and missing records.
Submission Links
What I Learned
- BETWEEN is inclusive on both ends - release_year BETWEEN 1990 AND 1999 catches 1990 and 1999 themselves
- ILIKE is PostgreSQL's case-insensitive pattern match - LIKE would miss titles where capitalization doesn't match exactly
- The % wildcard means "anything before or after" -%star% catches Star Wars, Interstellar, and A Star Is Born in one query
- WHERE column = NULL always returns 0 rows because NULL comparisons evaluate to UNKNOWN, not TRUE - IS NULL bypasses this entirely
- Schema column names carried forward from earlier weeks -genre became category and rating became viewer_rating after Week 3 ALTER operations
Key Takeaways
SQL filtering is only as reliable as your understanding of edge cases. BETWEEN looks simple but its inclusivity matters. ILIKE is the right default for any user-facing search. And NULL is not a value — it's the absence of one, which is why equality checks silently fail. The column name mismatch from Week 3 was a good reminder that schema changes have downstream effects across every query written after them.
StreamFlix "User Engagement" Analytics Challenge
Students will write precise PostgreSQL queries using INNER JOIN, LEFT JOIN, and FULL JOIN to connect a movie catalog with user review data.
Submission Links
What I Learned
- INNER JOIN returns only rows with matches in both tables — orphan ratings and unreviewed movies are both excluded
- LEFT JOIN returns all rows from the left table with NULLs where no match exists in the right table, useful for finding gaps in data
- FULL OUTER JOIN returns everything from both tables, surfacing both orphaned ratings and movies with no reviews in one query
- TRUNCATE does not reset sequences — use RESTART IDENTITY or ALTER SEQUENCE to avoid ID mismatches when reseeding
Key Takeaways
Choosing the right JOIN type depends on what you want to exclude, not just what you want to include. INNER JOIN is for active, complete data. LEFT JOIN finds missing relationships. FULL OUTER JOIN is a complete system audit. A schema rename from a previous week also carried forward here — a good reminder that real databases accumulate decisions over time and code has to adapt to them.
StreamFlix "User Engagement" Analytics Challenge
Students will write precise PostgreSQL queries using aggregation (GROUP BY, HAVING) and conditional logic (CASE, COALESCE) to build clean business intelligence reports in Supabase.
Submission Links
What I Learned
- GROUP BY aggregates rows into groups; HAVING filters those groups after aggregation, unlike WHERE which filters rows before
- COALESCE returns the first non-NULL value in a list, making it straightforward to substitute display-friendly fallbacks for missing data
- CASE expressions evaluate conditions top-down and return on the first match, similar to if/else logic in application code
- TRUNCATE without RESTART IDENTITY leaves the sequence at its prior position, causing new inserts to start from unexpected IDs
Key Takeaways
Aggregation queries build the foundation of most business reporting — knowing when to use HAVING versus WHERE is the key distinction. COALESCE and CASE are the tools for cleaning up how data is presented without changing what's stored. A recurring theme this week and last: seed data issues with sequences caused incorrect results that looked correct until inspected closely.