Common Table Expressions exercises for data analyst interviews

When tasked with analyzing sales data, many analysts find themselves writing complex SQL queries that quickly become cumbersome and hard to maintain. A common scenario involves calculating total sales per product and filtering for those with total sales above a certain threshold, such as $1000. Without effective use of Common Table Expressions (CTEs), these queries can become unwieldy. Let's explore how to leverage CTEs to simplify your SQL exercises and avoid common pitfalls during interviews and real-world applications.

Why Choose Common Table Expressions?

CTEs are a powerful feature in SQL that allow you to break complex queries into more manageable pieces, improving both readability and maintainability. They enable you to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. This modular approach reduces the risk of errors and clarifies your logic, aspects that interviewers often look for when evaluating a candidate's understanding during tests.

Example Data

Consider the following sales table containing sales data:

sales_id	product_id	amount
1	A	300
2	B	800
3	A	600
4	C	200
5	B	700

Task: Calculate Total Sales Per Product

You are asked to calculate the total sales per product and filter for products with total sales exceeding $1000. Without using a CTE, this task might transform into a complex nested query that is hard to decipher.

Query Without CTE:

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(amount) > 1000;

While this is a straightforward query, imagine needing to join other tables or incorporate additional filters. Complexity grows, making it harder to maintain.

Using a CTE for Clarity

Now, let’s see how using a CTE can enhance clarity:

WITH TotalSales AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
) 
SELECT product_id, total_sales
FROM TotalSales
WHERE total_sales > 1000;

In this revised example, the CTE TotalSales captures the logic for calculating total sales, making the subsequent query cleaner and more straightforward.

Common Interview Traps

As you prepare for interviews focusing on CTEs, be aware of the following common traps:

• Overcomplicating Queries: Candidates often try to do too much in a single query. Break your logic into CTEs; interviewers appreciate clean, modular code.
• Ignoring Readability: Failing to use CTEs can lead to complex syntax that's difficult for others (and yourself) to understand later. Always think about how someone else would read your code.
• Misunderstanding Scope: Remember that CTEs are scoped to the statement that follows them. If you try to reference a CTE outside its defined query, you'll run into issues.
• Not Considering Performance: While CTEs improve readability, they can sometimes lead to performance issues depending on the database engine. Be prepared to discuss when it’s more appropriate to use a subquery.

Worked Example

Let’s reason through a practical interview scenario: You have been provided the same sales table and asked to identify which products have total sales that exceed $1000. In your initial thought process using a nested query, you might have ended up with:

SELECT product_id, total_sales
FROM (SELECT product_id, SUM(amount) AS total_sales
      FROM sales
      GROUP BY product_id) AS SalesSummary
WHERE total_sales > 1000;

While this produces the correct results, let’s transition to a CTE-based approach for enhancement:

1. Define the CTE: Calculate total sales per product with a clear label.
2. Select from the CTE: Fetch results, applying your final filter.

The corresponding SQL would be:

WITH SalesSummary AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT product_id, total_sales
FROM SalesSummary
WHERE total_sales > 1000;

This clean structure reduces cognitive load and aligns with best practices expected in analytical roles.

On the Job: Common Use Cases

CTEs are invaluable for data analysts daily. You'll often deal with:

• Hierarchical data: Using recursive CTEs to parse hierarchical relationships, such as organizational structures or product categories.
• Data transformations: Simplifying data manipulation by breaking it into logical steps, making it easier to debug and maintain.
• Performance tuning: In cases where you need to reference complex aggregations multiple times in a report, using a CTE can optimize performance over recalculating the same aggregations.

In production, clarity and maintainability are not just nice-to-haves; they're essential. The decisions you make about query structure can affect everything from collaboration with teammates to the performance of reports.

References

• PostgreSQL Documentation on WITH Queries
• SQL Server Common Table Expressions
• Oracle documentation on WITH Clause
• MDN - SQL Tutorial
• SQLZoo - SQL Tutorial