SQL for Data Analytics
Advanced SQL patterns for data analysis and reporting.
Page 8: Building Complex Data Sets — Advanced SQL Techniques ⚙️
As projects grow, your SQL becomes more complex. Maintaining readability, performance, and accuracy requires moving beyond simple SELECT statements into architectural SQL.
This content is adapted from Mastering System Design from Basics to Cracking Interviews (Udemy). It has been curated and organized for educational purposes on this portfolio. No copyright infringement is intended.
SQL vs. ETL vs. Other Tools
- SQL: Best for logic that lives close to the data.
- ETL (dbt, Airflow): Best for managing dependencies between dozens of SQL models.
- Notebooks: Best for exploratory analysis that requires charts and prose intermixed.
Code Organization Best Practices
Comments
Explain WHY, not what. The SQL shows what is happening; your comments should explain the business logic or edge case decisions.
Formatting and Indentation
Use a consistent style (like the SQLFluff or dbt style guides). Leading commas, uppercase keywords, and clear aliases make code review easier.
CTEs over Subqueries
Always prefer Common Table Expressions (CTEs). They make your code readable from top to bottom and allow for better debugging by selecting from individual CTEs.
Understanding SQL Evaluation Order
The computer read your SQL in a different order than you write it:
FROM/JOINWHEREGROUP BYHAVINGSELECTORDER BYLIMIT
Knowing this helps you understand why aliases created in SELECT often can't be used in WHERE.
Advanced Aggregations: GROUPING SETS
When you need to generate reports with multiple levels of granularity in a single query (e.g., Total by Country, Total by Region, and Grand Total).
SELECT country, region, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS ((country, region), (country), ())Storing and Versioning
SQL should be treated as production code:
- Store snippets in Git.
- Use peer review for critical analytical queries.
- Modularize repetitive logic into Views or Macros.