Вивчайте Subqueries for Complex Analysis | Advanced SQL for Data Analysis

Subqueries are a powerful feature in SQL that allow you to embed one query within another. This technique opens up new possibilities for complex analysis, such as comparing individual values to calculated aggregates or filtering based on dynamic criteria.

Consider a situation where you want to find customers whose total purchases are above the average for all customers. Instead of calculating the average separately and plugging it into your query, you can use a subquery to handle both steps in one statement. This approach keeps your SQL concise, readable, and adaptable to changes in the data.


              12345678910111213
            
-- Find customers with total purchases above the average
SELECT c.customer_id, c.first_name, c.last_name, SUM(s.total_amount) AS total_purchases
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(s.total_amount) > (
    SELECT AVG(total_purchases)
    FROM (
        SELECT SUM(total_amount) AS total_purchases
        FROM sales
        GROUP BY customer_id
    ) AS customer_totals
);

You can use subqueries in several parts of an SQL statement. The most common locations are the SELECT clause (to calculate values for each row), the FROM clause (to define a derived table or virtual result set), and the WHERE clause (to filter results based on another query). Subqueries let you perform calculations or filtering that depend on aggregated, grouped, or otherwise intermediate results, all within a single query. This makes your analysis more flexible and can eliminate the need for temporary tables or manual calculations.


              123456789
            
-- Calculate total sales per product using a subquery in the FROM clause
SELECT p.product_id, p.product_name, sales_totals.total_sales
FROM products p
JOIN (
    SELECT product_id, SUM(total_amount) AS total_sales
    FROM sales
    GROUP BY product_id
) AS sales_totals
ON p.product_id = sales_totals.product_id;

1. Where can subqueries be used in SQL?

2. What is the main benefit of using a subquery?

3. Fill in the blanks to complete the query. Find all products whose total sales are above the average total sales for all products.

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 4. Розділ 1

Запитати АІ

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат

Suggested prompts:

Can you explain how the subquery in the HAVING clause works?

What are some other common use cases for subqueries in SQL?

Can you show an example of a subquery in the WHERE clause?

Свайпніть щоб показати меню