AI Engineer Path
Phase 1 · Prerequisites5 Oct – 11 Oct

Week 3

SQL · AI coding · DSA for AI

Clear three prerequisite nodes you mostly already hold.

Why this week matters

AI-assisted coding is a genuinely new skill and a force multiplier for the remaining 37 weeks. The DSA here is targeted: heaps for top-k and graphs for the vector search structures you will meet in week 30.

Done when

You have an AI-coding workflow you trust, and you can explain HNSW at a whiteboard.

Concepts

6 lessons · tick each one once you could explain it

Joins combine tables: INNER keeps matching rows only, LEFT keeps every row from the left side (with NULLs where nothing matched). GROUP BY collapses rows into groups for aggregates (COUNT, SUM, AVG); HAVING filters groups after aggregation, WHERE filters rows before.

A Common Table Expression names an intermediate result: WITH recent AS (...) SELECT ... FROM recent. Chain several to read a query top to bottom like a pipeline. Recursive CTEs walk hierarchies.

Why it matters for AI engineering: evaluation results, traces and cost logs all end up in tables, and pgvector puts your embeddings right next to them in Postgres.

sql
WITH per_model AS (
  SELECT model, COUNT(*) AS calls, SUM(cost_usd) AS cost
  FROM llm_calls
  WHERE created_at >= now() - interval '7 days'
  GROUP BY model
)
SELECT model, calls, cost, cost / calls AS cost_per_call
FROM per_model
ORDER BY cost DESC;

Going deeper

EXPLAIN ANALYZE shows how Postgres executes a query: sequential vs index scans, join algorithms (nested loop, hash, merge) and where the time goes. Reading plans is the fastest way to fix a slow query.

Recursive CTEs (WITH RECURSIVE) walk trees and graphs: org charts, category hierarchies, dependency chains.

Common pitfalls

  • COUNT(col) skips NULLs; COUNT(*) doesn't.
  • LEFT JOIN followed by a WHERE on the right table silently turns it into an INNER JOIN.

Best resources for this lesson

A window function looks at a 'window' of rows related to the current one, defined by OVER (PARTITION BY ... ORDER BY ...), and returns a value per row. Unlike GROUP BY, no rows disappear.

Core functions: ROW_NUMBER(), RANK(), DENSE_RANK() for rankings; LAG()/LEAD() to compare with previous/next rows; SUM() OVER (ORDER BY ...) for running totals; AVG() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for moving averages.

The classic pattern is top-N per group: rank within each partition, then filter on the rank in an outer query.

sql
-- best-scoring answer per question
SELECT * FROM (
  SELECT q_id, answer_id, score,
         ROW_NUMBER() OVER (PARTITION BY q_id ORDER BY score DESC) AS rn
  FROM eval_results
) t
WHERE rn = 1;

Going deeper

The frame clause controls which rows a window function sees: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (fixed row count) vs RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW (value-based). The default frame with ORDER BY is 'from the start up to the current row', which surprises people using LAST_VALUE.

NTILE, PERCENT_RANK and CUME_DIST compute quantiles and percentiles, handy for latency p95 by model or by day.

Common pitfalls

  • You can't filter on a window function in the same SELECT's WHERE; wrap it in a subquery or CTE.

Best resources for this lesson

Where this comes back

  • Week 14Lag features for time series are LAG() in disguise.

AI coding assistants (Claude Code, Cursor, Copilot) are most effective when you treat them as a fast junior collaborator. Context: point them at the relevant files, conventions and a project instructions file (like CLAUDE.md or Cursor rules). Specification: describe the outcome and the constraints, not just 'make it work'.

Test-first prompting is the highest-leverage habit: write (or have it write) a failing test that captures the requirement, then ask for the implementation that passes it. You get an objective definition of done and a guard against regressions.

Work in small, reviewable steps. Read every diff. Commit often so you can roll back a bad direction cheaply.

Going deeper

A strong loop: explore (have the agent read the relevant code and explain it), plan (agree an approach before any edits), implement in small steps with tests, verify (run tests, read the diff), commit. Planning first prevents large wrong turns.

Keep a project instructions file current with build commands, conventions and gotchas. It is context engineering for your own tools, and it compounds over the whole path.

Common pitfalls

  • Accepting large diffs you haven't read.
  • Letting the assistant 'fix' a failing test by changing the test.

Best resources for this lesson

Where this comes back

  • Week 28Context engineering for your own products uses the same idea: what is in the window decides the output.

Known failure modes: hallucinated APIs (methods or flags that don't exist, especially for fast-moving libraries), outdated patterns from older library versions, silent scope creep (refactoring things you didn't ask about), shallow fixes that make the symptom disappear, and security regressions (string-built SQL, disabled TLS verification, secrets in code).

Mitigations: run the code and the tests, check docs for any unfamiliar API, constrain scope explicitly ('only change function X'), and ask for an explanation of why a fix works.

When not to use it: when you are learning a concept for the first time (this whole path — type it yourself, especially Karpathy's weeks), security-critical logic you can't verify, and code you won't be able to maintain.

Going deeper

Package hallucination is a real supply-chain risk: models sometimes suggest plausible packages that don't exist, and attackers register those names ('slopsquatting'). Verify any unfamiliar dependency before installing it.

Assistants tend to agree with your framing. Ask for alternatives and counter-arguments explicitly ('what would make this approach wrong?'), and treat confident explanations of unfamiliar code as hypotheses to check.

Backend engineer tip: Review AI output exactly as you would review a pull request from someone you don't yet trust.

Best resources for this lesson

Hash maps give O(1) average lookup by key: deduplication, caching (semantic caches in week 36 are hash maps keyed on embeddings' nearest neighbours), counting.

A heap is a binary tree stored in an array where the parent is always ≤ its children (min-heap). Push and pop are O(log n). To keep the top-k scores from a stream, maintain a min-heap of size k: if a new score beats the smallest one in the heap, replace it.

This is exactly what a retriever does after scoring candidates: keep the k highest similarities. Python's heapq.nlargest(k, items, key=...) implements it.

top-k via heap: O(nlog⁡k)vs full sort: O(nlog⁡n)\text{top-}k \text{ via heap: } O(n \log k) \quad \text{vs full sort: } O(n \log n)
python
import heapq
scores = [(0.82, "doc3"), (0.91, "doc7"), (0.40, "doc1"), (0.77, "doc9")]
top2 = heapq.nlargest(2, scores)   # [(0.91,'doc7'), (0.82,'doc3')]

Going deeper

Other structures that show up in AI systems: tries (prefix matching in tokenizers and autocomplete), Bloom filters (cheap 'definitely not seen' checks for deduplicating crawled documents), MinHash/LSH (near-duplicate detection in training data), and LRU caches (response and embedding caches).

Merging top-k results from several shards is a k-way merge with a heap: each shard returns its local top-k and a heap combines them, which is how distributed vector search works.

Best resources for this lesson

Where this comes back

  • Week 30Every vector search returns top-k by similarity.

Exact nearest-neighbour search compares the query with every vector: O(n·d). At millions of vectors that is too slow, so we use approximate nearest-neighbour (ANN) indexes.

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph. Upper layers are sparse 'express lanes' with long links; the bottom layer contains every vector with short links. A search enters at the top, greedily hops to whichever neighbour is closest to the query, then drops a layer and repeats, refining as it descends. Parameters M (links per node) and ef (search breadth) trade speed, memory and recall.

IVF (inverted file) clusters vectors with k-means, then searches only the few clusters whose centroids are nearest the query (nprobe). PQ (product quantisation) compresses vectors so more fit in memory. IVF-PQ is the memory-frugal choice at very large scale.

HNSW is like travelling by motorway, then A-roads, then local streets: big jumps first, fine steps last.

Going deeper

HNSW's small-world property comes from mixing short links (local precision) with long links (fast navigation), like social networks where any two people are a few hops apart. Search cost grows roughly logarithmically with dataset size.

Index choice is a three-way trade-off between recall, latency and memory. Rough guide: under ~1M vectors, flat or HNSW in memory; tens of millions, HNSW with quantisation; billions, IVF-PQ or disk-based indexes such as DiskANN.

Common pitfalls

  • ANN is approximate: always measure recall@k against exact search on a sample.

Best resources for this lesson

Where this comes back

  • Week 30You will tune HNSW and benchmark recall@k in the vector databases week.

Practice

Hands-on work that makes the lessons stick. Warm-ups take minutes; stretch goals are optional.

  1. Warm-up

    Top-N per group

    On a sample dataset (e.g. a public sales or movies table), find the top 3 items per category with a window function, then compute a 7-day moving average with a frame clause.

  2. Core

    AI-pair a feature, test-first

    Pick a small feature. Have an AI assistant write failing tests first, then the implementation. Review the diff line by line and note every correction you had to make.

  3. Stretch

    Build a mini vector search

    Implement brute-force cosine top-k with NumPy and a heap, then compare speed and recall against Faiss's HNSW index on 100k random vectors.

This week, day by day

Dates follow your pace from Settings. Open the notebook icon to log hours and notes.

  1. Day 15Monday5 Oct2 h planned

    SQL refresher: joins, aggregates, subqueries, CTEs

  2. Day 16Tuesday6 Oct2 h planned

    SQL window functions and analytics patterns

  3. Day 17Wednesday7 Oct2 h planned

    AI Coding: Cursor / Claude Code setup, repo context, test-first prompting

  4. Day 18Thursday8 Oct2 h planned

    AI Coding: review workflows, known failure modes, when not to use it

  5. Day 19Friday9 Oct2 h planned

    DSA for AI: arrays/matrices, hashing, heaps (top-k)

  6. Day 20Saturday10 Oct3 h planned

    DSA for AI: graphs + similarity search structures (HNSW, IVF) intuition

  7. Day 21Sunday11 OctReview

    Review the week, finish anything unfinished, rest

Watch

SQL Tutorial: Full Database Course

freeCodeCamp

Software Is Changing (Again)

Andrej Karpathy · Y Combinator

How I use LLMs

Andrej Karpathy

HNSW for Vector Search, Explained and Implemented

James Briggs

Read and use

Interview prep

Questions this week's material gets asked as. Answer out loud first, then open the outline.

Explain HNSW to a colleague in two minutes.
  • Layered proximity graph: sparse long links on top, dense short links at the bottom
  • Greedy search from an entry point, descending layers
  • M and ef trade recall, speed and memory
  • Approximate: measure recall against brute force
How do you find the top 10 most similar items among 10 million?
  • Brute force is O(n·d): fine for small n, too slow here
  • ANN index (HNSW, IVF-PQ) for sub-linear search
  • Heap for top-k; shard and merge for distribution
How do you use AI coding tools without lowering code quality?
  • Small scoped tasks with tests as the spec
  • Review every diff; verify unfamiliar APIs
  • Project instruction files for conventions
  • Know when not to use them: learning, security-critical code

Check yourself

Five questions. The done-when test above is the real bar; this is a quick self-check.

  1. 1.What does a window function do that GROUP BY doesn't?

  2. 2.Best way to keep the 10 highest scores from a stream of 1M items?

  3. 3.In HNSW, what are the upper layers for?

  4. 4.An AI assistant 'fixes' a failing test by editing the test. What's the right reaction?

  5. 5.IVF search with nprobe=4 means…