SQL Engineering Handbook is a production-ready learning resource for aspiring and practicing Data Analysts who need mastery over SQL—from foundational SELECT statements to advanced window functions and business analytics.
Unlike tutorial blogs, this is a structured handbook with 100+ real-world SQL queries, each documented with:
- ✅ Business problem & context
- ✅ Step-by-step SQL solution
- ✅ Performance considerations
- ✅ Common mistakes & how to avoid them
- ✅ Interview tips & follow-up questions
- ✅ Practice challenges
Perfect for: Data Analyst interviews, portfolio building, SQL upskilling, or as a desk reference during work.
| Level | Topics |
|---|---|
| Beginner | SELECT, WHERE, ORDER BY, DISTINCT, LIMIT, ALIAS |
| Intermediate | GROUP BY, HAVING, CASE statements, JOINS, Subqueries, CTEs |
| Advanced | Window Functions, Recursive CTEs, Query Optimization, Views |
| Analytics | Customer Segmentation, Revenue Analysis, CLV, Retention, Cohort Analysis |
✅ SQL Fundamentals (5 topics)
✅ Aggregations & Grouping (6 topics)
✅ JOINS (Inner, Left, Right, Full, Cross)
✅ Subqueries & Common Table Expressions
✅ CASE When Statements
🔄 Window Functions (ROW_NUMBER, RANK, LAG/LEAD, Running Totals)
🔄 Query Optimization & Performance Tuning
🔄 Advanced Business Analytics
00_Resources/ Links to SQL tools, documentation, best practices
01_Fundamentals/ Core SQL queries (SELECT, WHERE, ORDER BY, etc.)
02_Aggregations/ GROUP BY, aggregate functions, HAVING clauses
03_Joins/ JOIN types with real-world use cases
04_Subqueries/ Scalar, inline, correlated subqueries
05_CASE_WHEN/ Conditional logic & data transformations
06_CTEs/ Common Table Expressions & recursion
07_Window_Functions/ Advanced analytical functions
08_Interview_Questions/50+ real interview prep questions
09_Business_Case_Studies/ End-to-end analytics projects
10_Schema/ Custom database schema & sample data
All queries use a production-like database schema (employees, departments, locations) so you practice on realistic data relationships:
employees → departments → locations
| Table | Columns |
|---|---|
| employees | emp_id, emp_name, dept_id, manager_id, hire_date |
| departments | dept_id, dept_name, location_id |
| locations | location_id, city |
Start with Fundamentals and progress through advanced topics:
01_Fundamentals → 02_Aggregations → 03_Joins → ... → 09_Business_Case_Studies
Jump to specific topics based on your needs:
- Preparing for interviews? →
08_Interview_Questions/ - Want to master Window Functions? →
07_Window_Functions/ - Building an analytics portfolio? →
09_Business_Case_Studies/
Bookmark this repo and search for SQL patterns you need on the job.
Every file includes:
- Business Context - Why you'd write this query in the real world
- SQL Solution - Production-ready, optimized code
- Explanation - What each part does and why
- Common Mistakes - What NOT to do and why it fails
- Interview Tips - Follow-up questions you might get asked
- Practice Questions - Test your understanding
This is not just code snippets—it's a complete learning system.
- Master SELECT, WHERE, ORDER BY
- Understand filtering and sorting
- Practice with simple queries
- Learn GROUP BY and aggregate functions
- Handle complex filtering with HAVING
- Build reporting queries
- Inner, Left, Right, Full joins
- Solve multi-table problems
- Understand data relationships
- Subqueries and CTEs
- Window functions for analytics
- Query optimization
- Customer segmentation
- Revenue analysis
- Business intelligence queries
SELECT
dept_name,
AVG(salary) AS avg_salary,
COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY dept_name
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;SELECT
emp_id,
emp_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank,
LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;Browse the repo for 100+ more examples with detailed explanations.
- Database: MySQL 8.0+
- Language: SQL (ANSI-standard with MySQL-specific features)
- Tools: Any SQL IDE (MySQL Workbench, DBeaver, SQLiteOnline, etc.)
- Clone or Fork this repository
- Set up a local MySQL database (or use SQLiteOnline for no setup)
- Load the schema from
10_Schema/ - Pick a topic and read the queries
- Run the queries in your SQL IDE
- Modify and experiment - change WHERE clauses, add new columns, etc.
- Practice the challenge questions at the end of each section
This handbook prepares you for:
- Data Analyst Interviews - Master the top 50 SQL questions asked in real interviews
- Portfolio Projects - Build end-to-end analytics projects
- Day-to-Day Work - Reference patterns for common analytics queries
- Career Growth - Progress from junior analyst to SQL expert
- Business Intelligence - Create KPI dashboards and reports
| Topic | Status | Practice Difficulty |
|---|---|---|
| Fundamentals | ✅ Complete | Easy |
| Aggregations | ✅ Complete | Easy-Medium |
| Joins | ✅ Complete | Medium |
| Subqueries | ✅ Complete | Medium |
| CTEs | ✅ Complete | Medium-Hard |
| Window Functions | 🔄 In Progress | Hard |
| Query Optimization | 🔄 In Progress | Hard |
| Business Analytics | 🔄 In Progress | Hard |
Found a bug? Have a better SQL solution? Want to add interview questions?
Contributions are welcome! Please:
- Fork the repository
- Create a branch for your feature
- Add clear documentation and explanations
- Submit a pull request
This project is licensed under the MIT License - see the LICENSE file for details.
Mohammad Ammar
Aspiring Data Analyst | SQL Enthusiast | Data Storyteller
If this handbook helped you:
- ⭐ Star this repository to show support
- 🔗 Share with aspiring data analysts on LinkedIn, Reddit, or Twitter
- 💬 Leave feedback in the discussions tab
- 📢 Mention it in your blog or portfolio
Your support helps this resource reach more people!
- Open an issue for bugs or suggestions
- Start a discussion for questions about specific queries
- Tag me @theammarngp-makes if you use this in your portfolio
Happy SQL learning! 🚀