SQL for data & AI

SQL is the bedrock

Every data and AI system runs on data that lives in tables, and SQL is how you shape it. Interviews test fluency, not trivia. The essentials beyond basic SELECT:

• Joins — INNER (matches only), LEFT (all left rows), and knowing when a join fans out (a one-to-many join multiplying rows) — a top source of wrong aggregates.
• Aggregation — GROUP BY + HAVING (filter after aggregation, vs WHERE before). Know the difference cold.
• Window functions — the skill that separates strong candidates. ROW_NUMBER / RANK / DENSE_RANK, LAG / LEAD, running totals and moving averages with SUM() OVER (...), and PARTITION BY. They compute across rows without collapsing them — essential for ranking, deduplication, sessionization, and time-series.
• CTEs (WITH) — break a complex query into readable, testable steps. Prefer over nested subqueries.
• Window frames — ROWS (physical row count) vs RANGE (value-based) matter for correct running calculations.

Query performance

• Indexes — speed lookups on filtered/joined columns; the difference between a query in milliseconds and minutes. Understand that an index helps reads but costs writes.
• Read the plan — EXPLAIN shows whether you're doing a full scan vs an index seek; the fix for a slow query usually starts here.
• Avoid the traps — functions on indexed columns (breaks the index), SELECT * on wide tables, and accidental cross joins.

SQL for AI systems

AI apps still lean on SQL: pulling training/eval data, storing conversations and submissions, joining features, and — with pgvector — even vector similarity search in Postgres. And LLM apps increasingly generate SQL (text-to-SQL), so understanding what makes a query correct and efficient is doubly relevant.