Data engineering & analytics
Query and model data with SQL
Joins, aggregations, window functions, CTEs; answer business questions from relational data.
~25 focused hours·beginner
Tools: PostgreSQL, BigQuery, MySQL, DBeaver / TablePlus, window functions, CTEs
Market relevance — share of job ads asking for this
What employers mean
You should be able to…
- Write multi-table joins to answer a business question from raw relational data
- Use window functions (RANK, LAG/LEAD, running totals) for cohort and trend analysis
- Write CTEs to break a gnarly query into readable steps instead of nested subqueries
- Aggregate and group data correctly (avoid double-counting from fan-out joins)
- Optimize a slow query using EXPLAIN and appropriate indexes
- Validate query output against a known ground truth before shipping a number
- Translate a vague stakeholder ask ('why did signups drop') into a precise SQL question
Learn — free, link-checked
The few resources that matter
Read · beginner · 20 min · postgresql.org
The SQL Language — Joins Between Tables
Canonical explanation of inner/outer/cross joins from the database you'll actually be tested on in interviews. — PostgreSQL Global Development Group
Course · beginner · 150 min · mode.com
SQL Tutorial
Interactive, browser-based SQL lessons that go from SELECT basics to window functions using real business questions — no local setup. — Mode Analytics
Watch · beginner · 260 min · youtube.com
SQL Tutorial - Full Database Course for Beginners
One sitting from installing a database to joins, subqueries, and schema design — the fastest way to get query-writing reps in. — freeCodeCamp.org
Read · intermediate · 30 min · postgresql.org
The SQL Language — Window Functions
Official reference for RANK, LAG, and running totals — the SQL feature most Indian AI-analyst interviews probe for. — PostgreSQL Global Development Group
Practice
UPI transaction anomaly dashboard (SQL-only)
Load a synthetic UPI-style transactions dataset (payer, payee, amount, timestamp, status) into Postgres and answer 10 business questions purely in SQL: daily volume trend, top failure reasons, repeat-failure payers, and a window-function-based 'flag transactions where the same payer sends 5+ payments within 10 minutes' anomaly query. Keep every query in a single reproducible .sql file with comments explaining the logic.
Done when
- Schema and seed data are scripted (not manually inserted) so the project is reproducible from scratch
- At least 3 queries use window functions and at least 2 use CTEs
- One query correctly flags a velocity-based anomaly (multiple transactions in a short window per payer)
- README documents each query's business question and includes the query plan (EXPLAIN output) for the slowest one
Prove it
Evidence a recruiter can check
- Public GitHub repo with the .sql files, seed script, and a README explaining each business question answered
- A short Loom/video walkthrough (3-5 min) narrating one non-trivial query and why you wrote it that way
- A completed, screenshot-able Mode SQL tutorial or Kaggle SQL micro-course certificate
- LeetCode/StrataScratch/HackerRank SQL profile with medium+ problems solved, linked from the repo
Interview
Questions you'll get asked
- Write a query to find the second-highest salary per department using window functions.
- Given orders and customers tables, find customers with no orders in the last 90 days.
- What's the difference between RANK, DENSE_RANK, and ROW_NUMBER? When would each cause a bug?
- How would you find and de-duplicate rows that are logically the same but differ in one column?
- Explain the difference between WHERE and HAVING with an example.
- A join is returning more rows than expected — how do you debug it?
- Write a query for month-over-month retention using a self-join or window function.
- How would you find the top 3 products by revenue within each category (not overall)?