Today was the heaviest single day of the challenge so far.
Three challenges. Pure databases. From writing raw SQL queries like a data analyst to building a full stack FastAPI application backed by PostgreSQL. All in one sitting.
## Challenge 1: Query an Existing Database Like a Data Analyst
No new tables. Pure querying against existing leads and notes tables. Eight real business questions answered using SQL.
What I wrote:
- Which lead has the most notes? Show their name, email and note count
- Show all leads and their most recent note. Leads with no notes appear with NULL
- How many leads are in each status? Sorted by count descending
- Which sources produced more than one lead?
- Show all notes written by a specific author with the lead name next to each
- Find all leads who have never had a note written by that author
- Show the first note ever written for each lead
- Show leads whose status is not new and who have at least one note
The hardest query was number 6, finding leads with no notes from a specific author required thinking about what is absent rather than what is present. Subqueries started making real sense here.
## Challenge 2: Build a SQLAlchemy Model from Scratch
A completely new SQLAlchemy model file for a products and orders system — built without copying anything.
What I built:
- Product model with id, name, price, stock and created_at
- Order model with id, product_id as foreign key, quantity, total_price and created_at
- Bidirectional relationship so order.product and product.orders both work
- Tables created in PostgreSQL using Base.metadata.create_all()
- 4 products and 6 orders inserted using SQLAlchemy sessions — no raw SQL
- Python logic to find the most expensive order and print product name, quantity and total price
- Stock update logic that deducts after an order is placed
## Challenge 3: Full Database Layer in FastAPI
A completely new FastAPI application managing products and orders, backed by the SQLAlchemy models from Challenge 3. Six endpoints built and tested in the docs UI.
Endpoints built and tested:
GET /products: all products with stock levels
POST /products: create a new product
GET /products/{id}L: one product with all its orders
POST /orders: place an order, stock deducts automatically. Returns 400 if stock is insufficient
GET /orders: all orders with product name next to each
DELETE /products/{id}: deletes product and all its orders
Then tested the full workflow manually:
Added a product, confirmed it appeared, retrieved it with orders, placed an order and confirmed stock deducted, retrieved all orders with product names, deleted a product and confirmed its orders disappeared too.
Also practiced truncating the database to reset state cleanly between test runs.
## What I Learned
Three things that clicked today that I will carry into every project:
Subqueries think about absence not presence. Finding what does not exist requires a completely different mental model from finding what does.
SQLAlchemy sessions are the right way to talk to a database from Python. Raw SQL works but ORM models make the code readable, maintainable and safe.
Full stack means every layer has a job. PostgreSQL stores it. SQLAlchemy talks to it. FastAPI exposes it. Each layer does one thing and does it well.
No GitHub link today, pure challenge day documented publicly.
41 more to go.
