Skip to content

CONTRIBUTING.md

Latest commit

 

History

History
533 lines (409 loc) · 14.7 KB

CONTRIBUTING.md

File metadata and controls

533 lines (409 loc) · 14.7 KB
Raw

Contributing to SQL Engineering Handbook

First off, thank you for considering contributing to the SQL Engineering Handbook! 🎉

This is a community-driven project, and we welcome contributions from everyone—whether you're a SQL expert or just starting out. Every contribution helps other developers learn and grow.

Table of Contents

Ways to Contribute

1. 🌍 Translate Queries to Other SQL Dialects

The Goal: Make the handbook useful for developers using different databases.

Current Focus: MySQL

Needed Translations:

  • ❄️ Snowflake SQL - Cloud data warehouse favorite
  • 🔷 Google BigQuery - Popular in data teams
  • 🗃️ Databricks / Apache Spark SQL - Big data & ML workflows
  • 🏗️ T-SQL (SQL Server) - Enterprise standard
  • 🐘 PostgreSQL - Open source, widely used

How to Contribute:

  1. Pick a file from any section (e.g., 01_Fundamentals/01_SELECT.sql)
  2. Translate the query to your target dialect
  3. Create a new file with the same name and add a suffix: 
    01_SELECT.sql           (original MySQL)
01_SELECT_snowflake.sql (Snowflake version)
01_SELECT_bigquery.sql  (BigQuery version)
  4. Include dialect-specific comments: 
    -- Snowflake Version
-- Key differences from MySQL:
-- 1. ILIKE is case-insensitive (MySQL: LOWER(col) LIKE pattern)
-- 2. String functions: use :: instead of CAST
-- 3. Date functions: DATE_PART instead of EXTRACT

SELECT * FROM employees WHERE emp_name ILIKE '%john%';
  5. Submit a Pull Request with your translations

Example Structure:

03_Joins/
├── 01_Inner_Join.sql              (MySQL - original)
├── 01_Inner_Join_snowflake.sql    (Snowflake)
├── 01_Inner_Join_bigquery.sql     (BigQuery)
├── 01_Inner_Join_tsql.sql         (SQL Server)
└── 01_Inner_Join_postgres.sql     (PostgreSQL)

Translation Checklist:

  • ✅ Query produces same results as MySQL version
  • ✅ Includes dialect-specific comments
  • ✅ No external dependencies (use built-in functions)
  • ✅ Performance-optimized for that dialect
  • ✅ Tests on actual database if possible

2. 🐛 Report Bugs & Issues

Found an error in a query? Syntax issue? Let us know!

Before Opening an Issue:

  • Check if the issue already exists
  • Test the query yourself (if possible)
  • Have the exact error message ready

When Opening an Issue:

  • Use the "Bug Report" template (it'll auto-populate)
  • Include: database type, query, expected output, actual output
  • Screenshots/errors are helpful

Example Bug Report:

**Title:** Error in 05_CASE_WHEN - Customer Tier Query

**Description:** The query returns NULL for customers with NULL salaries

**Database:** MySQL 8.0

**Query File:** 05_CASE_WHEN/03_Customer_Tier.sql

**Actual Output:**
emp_id | tier
------|-----
1     | Gold
2     | NULL  ← Should be "Bronze"

**Expected Output:**
emp_id | tier
------|-----
1     | Gold
2     | Bronze

3. 💡 Suggest Features & New Content

Have ideas for new queries, topics, or case studies? We'd love to hear them!

Feature Request Ideas:

  • New SQL concepts (e.g., "Recursive CTEs with Real Examples")
  • New business case study (e.g., "Marketing Attribution Analysis")
  • Performance optimization for specific queries
  • New database dialect support
  • Visual diagrams or query flow charts

How to Suggest:

  1. Open an issue with the "Feature Request" template
  2. Clearly describe what should be added
  3. Explain why it would be valuable
  4. Provide examples if possible

Example Feature Request:

**Title:** Add Query Optimization Tips Section

**Description:**
Many beginners write slow queries without knowing why. 
A dedicated section showing BEFORE/AFTER optimization would help.

**Suggested Content:**
- Common performance anti-patterns
- EXPLAIN ANALYZE walkthroughs
- Indexing strategies
- Real performance improvements (+examples)

**Why It Matters:**
- Interview prep (optimization is asked frequently)
- Real-world importance (slow queries cost money)
- Career growth (optimization skills are valuable)

4. ✍️ Write New Queries & Case Studies

Want to add a query? Submit one!

Query Template:

-- ============================================================
-- BUSINESS PROBLEM
-- ============================================================
-- Find the top 3 departments by average employee salary
-- and show how many employees are in each department.
--
-- Use Case: HR team wants to understand compensation by department

-- ============================================================
-- SOLUTION
-- ============================================================
SELECT 
    d.dept_id,
    d.dept_name,
    COUNT(e.emp_id) AS emp_count,
    ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY avg_salary DESC
LIMIT 3;

-- ============================================================
-- EXPLANATION
-- ============================================================
-- 1. JOIN: Connect employees to departments
-- 2. GROUP BY: Group by department
-- 3. COUNT: Count employees per department
-- 4. AVG: Calculate average salary per department
-- 5. ORDER BY + LIMIT: Get top 3

-- ============================================================
-- COMMON MISTAKES
-- ============================================================
-- ❌ MISTAKE 1: Forgetting GROUP BY with aggregate functions
-- SELECT d.dept_name, AVG(salary) -- Missing: GROUP BY
-- This will error in MySQL (strict mode)

-- ❌ MISTAKE 2: Using HAVING without GROUP BY context
-- SELECT AVG(salary) HAVING AVG(salary) > 50000 -- Wrong!

-- ✅ CORRECT: Always use GROUP BY with aggregates

-- ============================================================
-- INTERVIEW TIPS
-- ============================================================
-- Q: What if there are departments with NULL employees?
-- A: Use LEFT JOIN to include departments with no employees

-- Q: How would you handle NULL salaries?
-- A: Use WHERE salary IS NOT NULL or COALESCE(salary, 0)

-- Q: How would you optimize this for 1M+ employees?
-- A: Add indexes on dept_id and salary columns

-- ============================================================
-- PRACTICE QUESTIONS
-- ============================================================
-- 1. Modify to show ALL departments (including ones with no employees)
-- 2. Add a column showing salary quartile ranking within each dept
-- 3. Filter to only show departments with avg salary > $60k
-- 4. Add a running total of employees sorted by avg salary

Where to Contribute Queries:

  • New fundamentals? → 01_Fundamentals/
  • Interview question? → 08_Interview_Questions/
  • Business case? → 09_Business_Case_Studies/
  • General query? → Create appropriate section

5. 🚀 Optimize Existing Queries

Found a way to make a query faster or more readable?

Optimization Guidelines:

  • Include both original + optimized versions
  • Document the improvement (time saved, readability, clarity)
  • Add comments explaining the optimization
  • Show performance comparison if possible

Example Optimization:

-- ❌ ORIGINAL (Slower)
SELECT *
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
  AND YEAR(e.hire_date) = 2023;

-- ✅ OPTIMIZED (Faster)
-- Use CTE to calculate avg salary once instead of in WHERE clause
WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT e.*
FROM employees e
CROSS JOIN avg_salary a
WHERE e.salary > a.avg_sal
  AND YEAR(e.hire_date) = 2023;

-- Performance: 40% faster on large datasets
-- Reason: Subquery in WHERE clause was recalculating for each row

6. 📚 Improve Documentation

  • Better explanations for complex queries
  • Add visual diagrams
  • Create learning guides
  • Translate documentation to other languages

Getting Started

Step 1: Fork the Repository

git clone https://github.com/theammarngp-makes/SQL-Engineering-Handbook.git
cd SQL-Engineering-Handbook

Step 2: Create a Branch

git checkout -b feature/your-contribution-name
# Examples:
# feature/snowflake-translations
# feature/new-window-functions
# fix/aggregations-null-handling

Step 3: Make Your Changes

  • Add/modify files
  • Follow the formatting guidelines below
  • Test thoroughly

Step 4: Commit with Clear Messages

git commit -m "Add: Snowflake translations for Joins section"
# Or
git commit -m "Fix: Handle NULL values in aggregation queries"
# Or
git commit -m "Optimize: Use CTE instead of subquery for performance"

Step 5: Push and Create a Pull Request

git push origin feature/your-contribution-name

Then open a Pull Request with:

  • Clear title
  • Description of what you changed and why
  • Link to related issues (if any)

Code of Conduct

We are committed to providing a welcoming and inclusive environment:

✅ DO:

  • Be respectful and helpful
  • Welcome feedback and suggestions
  • Give credit where credit is due
  • Assume good intentions

❌ DON'T:

  • Use offensive or demeaning language
  • Harass or discriminate
  • Spam or self-promote excessively
  • Ignore feedback or criticism

Violation? Report to: theammarngp@gmail.com

Contribution Guidelines

SQL Query Format

File Naming:

{number}_{brief_description}.sql
Examples:
- 01_SELECT_Basics.sql
- 02_WHERE_Clause.sql
- 03_INNER_JOIN.sql

Query Structure:

-- ============================================================
-- BUSINESS PROBLEM
-- ============================================================
-- [2-3 sentence description of what the query does]
-- [Real-world use case]

-- ============================================================
-- SOLUTION
-- ============================================================
[SQL Query]

-- ============================================================
-- EXPLANATION
-- ============================================================
-- [Step-by-step explanation of the query]

-- ============================================================
-- COMMON MISTAKES
-- ============================================================
-- ❌ [Common mistake]
-- ✅ [Correct approach]

-- ============================================================
-- INTERVIEW TIPS
-- ============================================================
-- Q: [Potential follow-up question]
-- A: [Your answer or approach]

-- ============================================================
-- PRACTICE QUESTIONS
-- ============================================================
-- 1. [Variation of the query]
-- 2. [Related problem to solve]

SQL Best Practices

  • Use uppercase for keywords: SELECT, FROM, WHERE
  • Use lowercase for column/table names
  • Indent for readability
  • Add aliases to make queries clear
  • Include comments for complex logic
  • Test on actual data before submitting

Dialect-Specific Translations

When translating to another SQL dialect:

  1. Test thoroughly - Run on actual database
  2. Document differences - Add comments explaining dialect-specific syntax
  3. Maintain readability - Keep query structure similar to original
  4. Performance - Optimize for that dialect (indexes, functions, etc.)
  5. Edge cases - Handle NULLs, special characters, data types appropriately

Dialect Translation Checklist:

- [ ] Query tested on actual [Snowflake/BigQuery/etc.] instance
- [ ] Produces same results as MySQL version
- [ ] Includes dialect-specific comments
- [ ] Uses native functions (not workarounds)
- [ ] Performance-optimized for that database
- [ ] File named: {original_name}_{dialect}.sql
- [ ] Explains key differences from MySQL

Pull Request Process

Before Submitting

  1. ✅ Test your changes thoroughly
  2. ✅ Follow the SQL format guidelines
  3. ✅ Add clear commit messages
  4. ✅ Reference any related issues
  5. ✅ Update README if adding new section

What We'll Review

  • Correctness: Does the query work correctly?
  • Clarity: Is it easy to understand?
  • Usefulness: Does it add value to the handbook?
  • Format: Does it follow our guidelines?
  • Performance: Is it optimized?

Response Time

  • We review PRs within 48-72 hours
  • We may ask for changes
  • Once approved, we'll merge and credit you!

Recognition

Contributors Wall

We use the All Contributors bot to automatically recognize all contributions.

Your contributions earn badges for:

  • 🐛 Bug fixes
  • ✏️ Documentation
  • 🔧 Code (translations, new queries)
  • 💡 Ideas/suggestions
  • 📝 Writing content
  • 📢 Promotion

View the Contributors Wall: See the bottom of our README.md for the complete contributors section with profile pictures!

Hall of Fame

Top contributors (5+ contributions) get featured in our:

  • README.md contributors section
  • Monthly newsletter (if applicable)
  • GitHub profile mention

Questions or Need Help?

Resources

Types of Contributions We Need Most

🏆 High Priority:

  1. Snowflake SQL translations (highest demand)
  2. BigQuery SQL translations
  3. SQL Server / T-SQL translations
  4. New business case studies
  5. Interview question additions

Medium Priority:

  1. Documentation improvements
  2. Query optimizations
  3. PostgreSQL translations
  4. Bug fixes

Ongoing:

  1. Feedback and suggestions
  2. Sharing the handbook with others
  3. Real-world examples and use cases

Financial Recognition

As of now, this is a volunteer-driven open-source project. However:

  • If you contribute 10+ high-quality items, we'll feature you prominently
  • We're exploring open-source sponsorship opportunities
  • Your contributions build your portfolio and resume

License

By contributing to this project, you agree that your contributions will be licensed under the same MIT License.

Special Thanks

This project thrives because of contributors like YOU!

Whether you translate a single query, fix a typo, or write an entire case study—every contribution matters.

Thank you for making SQL learning better for everyone! 🙌

Ready to contribute?

  1. Fork the repo
  2. Create your feature branch
  3. Make your changes
  4. Submit a Pull Request
  5. Get featured as a contributor! ⭐

For questions, reach out on GitHub or via email. Happy contributing! 🚀