Writing Maintainable SQL Queries

1. The "Why" and "What" of SQL Style Guides

Just like Python has PEP 8, data teams use SQL style guides to ensure their code is consistent and high-quality. A style guide is a set of conventions for writing code that eliminates ambiguity and personal preference, allowing the team to focus on the logic of the analysis rather than debating formatting.

The following video provides a great overview of the three main pillars of a SQL style guide.

The key takeaway is that consistency is the primary goal. While different organizations might have slightly different rules (e.g., uppercase vs. lowercase keywords), adhering to a single style within a project is what truly matters.

2. Crafting Readable and Maintainable Queries

Now let's dive into the specific rules that make up a robust style guide. We will look at a few best practices that are widely adopted in the industry.

Formatting and Explicitness

First, let's establish some foundational rules for formatting. The guide below, from Mozilla's data team, offers a clear and practical set of conventions.

To summarize the most common conventions you'll encounter:

• Keywords: SELECT, FROM, WHERE, JOIN should be in UPPERCASE.
• Identifiers: Table names, column names, and aliases should be in lowercase, using snake_case (e.g., order_value).
• Indentation: Use consistent indentation (e.g., 4 spaces) to show hierarchy.
• Explicitness: Always use the AS keyword for aliases and specify the join type (e.g., INNER JOIN instead of just JOIN).
• Line Breaks: Place each major clause (SELECT, FROM, WHERE, GROUP BY) on a new line. List selected columns on separate lines for readability.

Structuring with CTEs and Other Tricks

Good structure goes beyond formatting. How you organize the logic of your query has the biggest impact on maintainability. As we've seen, Common Table Expressions (CTEs) are your most powerful tool for this.

The next video highlights several practical tricks, emphasizing how CTEs and other choices make your queries better.

Another critical rule for maintainability, mentioned in the video, is to avoid using SELECT * in production code. Always specify the columns you need. This makes the query's intent clear, prevents errors if the source table changes, and can improve performance.

Let's see these rules in action.

Bad: Hard to Read and Maintain

select c.customer_id, c.customer_name, count(o.order_id) from customers c join orders o on c.customer_id=o.customer_id where c.signup_date > '2023-01-01' group by c.customer_id, c.customer_name having count(o.order_id) > 1;

Good: Clear, Structured, and Maintainable

/*
This query identifies high-value customers who signed up in 2023 or later
and have placed more than one order.
*/
WITH customer_orders AS (
    -- Step 1: Join customers and orders to get order details for each customer.
    SELECT
        c.customer_id,
        c.customer_name,
        c.signup_date,
        o.order_id
    FROM
        customers AS c
    INNER JOIN
        orders AS o
            ON c.customer_id = o.customer_id
),

filtered_customers AS (
    -- Step 2: Filter for customers who signed up in the relevant period.
    SELECT
        customer_id,
        customer_name,
        order_id
    FROM
        customer_orders
    WHERE
        signup_date >= '2023-01-01'
)

-- Step 3: Aggregate to find customers with multiple orders.
SELECT
    customer_id,
    customer_name,
    count(order_id) AS total_orders
FROM
    filtered_customers
GROUP BY
    1, 2 -- Using positional references for conciseness
HAVING
    total_orders > 1
ORDER BY
    total_orders DESC;

Notice how descriptive CTE names (customer_orders, filtered_customers) and comments explain the purpose of each step, making the query self-documenting.

3. Documenting Your Work

There are two primary ways to document your SQL code:

1. In-Query Comments: Use comments to explain the why behind your code, not the what. Explain complex business logic, tricky calculations, or why a particular filter was chosen.

   • -- Single-line comment
   • /* Multi-line comment */

2. External Documentation: For a portfolio project, this is your README.md file. As you learned in the previous lesson, this file should explain the business context, your analytical process, key findings, and recommendations.

A well-commented query and a thorough README file work together to tell the complete story of your analysis.

4. Your Turn: Refactor Your Project Query

Now it's time to apply these principles. Your task is not to write a new analysis, but to elevate the work you've already done.

1. Open the SQL file for the Product Performance Report you created in the last lesson.
2. Refactor the entire query to adhere to the style guidelines discussed today. Specifically:
   • Ensure all keywords are UPPERCASE and all identifiers (tables, columns, aliases) are lowercase_with_snake_case.
   • Check that you've used explicit AS for all aliases and INNER JOIN (or LEFT JOIN) for all joins.
   • Fix any indentation or line-break issues to improve readability. Each column in the SELECT list should be on its own line.
   • Make sure your CTEs have clear, descriptive names.
3. Add comments to your query.
   • Add a multi-line comment block /* ... */ at the top explaining the overall goal of the query.
   • Add single-line comments -- to explain the purpose of each CTE and any complex logic within them (e.g., a tricky CASE statement or a window function).

Your goal is to transform your query into a professional, maintainable piece of code that another analyst could pick up and understand immediately.

Conclusion

In this lesson, you've learned how to structure and document your SQL queries like a professional data analyst. Writing clean, maintainable code is a hallmark of a skilled practitioner and is essential for effective collaboration and long-term project success.

Key Takeaways:

• Style guides create consistency and improve code quality.
• Key formatting rules include casing (uppercase keywords, lowercase identifiers), consistent indentation, and being explicit (AS, join types).
• CTEs are the best way to structure complex logic into readable, sequential steps.
• Comments should explain the "why" (business logic), not the "what" (obvious syntax).
• Avoid SELECT * in favor of explicitly naming columns to improve clarity and prevent future errors.

You have now completed all the core technical lessons in the SQL module. You can write queries ranging from simple selections to complex, multi-stage analyses and can format them to a professional standard.

In our final lesson for this module, we will focus on presenting your work. You will learn how to publish your SQL analysis project to GitHub, creating a polished portfolio piece that effectively showcases your skills to potential employers.