🚀 Advanced Querying

Once you've mastered basic SELECT statements, you're ready to tackle more complex queries. Advanced querying techniques allow you to solve sophisticated problems and perform deep data analysis.

🎯 Subqueries (Nested Queries)

Subqueries are queries within queries. They're useful when you need to reference the result of one query in another.

Types of Subqueries:

• **Scalar subqueries** - Return a single value

• **Row subqueries** - Return a single row

• **Table subqueries** - Return multiple rows and columns

Example 1: Scalar Subquery in WHERE Clause

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find all books with price above the average price
cursor.execute("""
    SELECT title, price
    FROM books
    WHERE price > (SELECT AVG(price) FROM books)
    ORDER BY price DESC
""")

above_average = cursor.fetchall()
print("Books above average price:")
for title, price in above_average:
    print(f"  {title}: ${price:.2f}")

conn.close()

Example 2: Subquery in FROM Clause

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find genres with more than 5 books
cursor.execute("""
    SELECT genre, book_count
    FROM (
        SELECT genre, COUNT(*) as book_count
        FROM books
        GROUP BY genre
    ) as genre_summary
    WHERE book_count > 5
    ORDER BY book_count DESC
""")

popular_genres = cursor.fetchall()
for genre, count in popular_genres:
    print(f"{genre}: {count} books")

conn.close()

🔗 UNION and UNION ALL

Combine results from multiple SELECT statements into a single result set.

• **UNION** - Removes duplicates

• **UNION ALL** - Keeps duplicates

Example 3: Combining Results with UNION

import sqlite3

conn = sqlite3.connect('library.db')
cursor = conn.cursor()

# Get authors from both fiction and non-fiction who have bestsellers
cursor.execute("""
    SELECT author, 'Fiction Bestseller' as category
    FROM books
    WHERE genre = 'Fiction' AND sales > 100000
    
    UNION
    
    SELECT author, 'Non-Fiction Bestseller'
    FROM books
    WHERE genre = 'Non-Fiction' AND sales > 100000
    ORDER BY author
""")

bestselling_authors = cursor.fetchall()
for author, category in bestselling_authors:
    print(f"{author} - {category}")

conn.close()

⚔️ HAVING Clause

The `HAVING` clause filters groups (after GROUP BY), unlike WHERE which filters rows before grouping.

Example 4: Filtering Groups with HAVING

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find genres with an average price above $25
cursor.execute("""
    SELECT
        genre,
        COUNT(*) as book_count,
        AVG(price) as average_price
    FROM books
    GROUP BY genre
    HAVING AVG(price) > 25
    ORDER BY average_price DESC
""")

expensive_genres = cursor.fetchall()
print("Genres with average price > $25:")
for genre, count, avg_price in expensive_genres:
    print(f"  {genre}: {count} books, avg ${avg_price:.2f}")

# Find customers who have made more than 10 purchases
cursor.execute("""
    SELECT
        customer_id,
        customer_name,
        COUNT(*) as purchase_count,
        SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id, customer_name
    HAVING COUNT(*) > 10
    ORDER BY purchase_count DESC
""")

loyal_customers = cursor.fetchall()
print("\nLoyalty Program Members (10+ purchases):")
for cid, name, count, total in loyal_customers:
    print(f"  {name}: {count} purchases, ${total:.2f}")

conn.close()

📊 Window Functions

Window functions perform calculations across a set of rows related to the current row. They're powerful for ranking, running totals, and comparisons.

Common Window Functions:

• `ROW_NUMBER()` - Assigns sequential number to rows

• `RANK()` - Assigns rank with gaps

• `DENSE_RANK()` - Assigns rank without gaps

• `LAG()` / `LEAD()` - Access previous/next row

• `SUM()` OVER - Running total

• `AVG()` OVER - Moving average

Example 5: Window Functions for Ranking

import sqlite3

conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

# Rank products by sales within each category
cursor.execute("""
    SELECT
        product_name,
        category,
        sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_category
    FROM products
    WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3
""")

# Note: SQLite has limited window function support, use:
cursor.execute("""
    SELECT
        product_name,
        category,
        sales
    FROM products
    ORDER BY category, sales DESC
""")

products = cursor.fetchall()
current_category = None
rank = 1
print("Top 3 products per category:")
for product, category, sales in products:
    if category != current_category:
        current_category = category
        rank = 1
        print(f"\n{category}:")
    if rank <= 3:
        print(f"  #{rank}: {product} - ${sales}")
        rank += 1

conn.close()

📝 Common Table Expressions (CTEs)

CTEs (also called WITH clauses) allow you to create temporary named result sets referenced within a SELECT statement. They make complex queries more readable.

Syntax:

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

Example 6: Single CTE

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Create a CTE for high-value customers, then analyze their behavior
cursor.execute("""
    WITH high_value_customers AS (
        SELECT
            customer_id,
            customer_name,
            SUM(amount) as total_spent
        FROM orders
        GROUP BY customer_id, customer_name
        HAVING SUM(amount) > 1000
    )
    SELECT
        hvc.customer_name,
        hvc.total_spent,
        COUNT(o.order_id) as order_count,
        AVG(o.amount) as avg_order_value
    FROM high_value_customers hvc
    JOIN orders o ON hvc.customer_id = o.customer_id
    GROUP BY hvc.customer_id, hvc.customer_name
    ORDER BY hvc.total_spent DESC
""")

results = cursor.fetchall()
print("High-Value Customer Analysis (>$1000 spent):")
for name, total, count, avg in results:
    print(f"  {name}: ${total:.2f} total, {count} orders, ${avg:.2f} avg")

conn.close()

Example 7: Multiple CTEs (Recursive Example)

import sqlite3

conn = sqlite3.connect('company.db')
cursor = conn.cursor()

# Multiple CTEs to analyze sales performance
cursor.execute("""
    WITH monthly_sales AS (
        SELECT
            strftime('%Y-%m', order_date) as month,
            SUM(amount) as sales
        FROM orders
        GROUP BY strftime('%Y-%m', order_date)
    ),
    sales_with_prior AS (
        SELECT
            month,
            sales,
            LAG(sales) OVER (ORDER BY month) as prior_month_sales
        FROM monthly_sales
    )
    SELECT
        month,
        sales,
        ROUND(((sales - prior_month_sales) / prior_month_sales * 100), 2) as growth_percent
    FROM sales_with_prior
    WHERE prior_month_sales IS NOT NULL
    ORDER BY month
""")

growth_data = cursor.fetchall()
print("Month-over-Month Sales Growth:")
for month, sales, growth in growth_data:
    direction = "↑" if growth > 0 else "↓"
    print(f"  {month}: ${sales:.2f} ({direction} {abs(growth):.1f}%)")

conn.close()

🔄 Correlated Subqueries

A correlated subquery references columns from the outer query. Each row in the outer query is evaluated against the subquery.

Example 8: Correlated Subquery

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find books that are above the average price for their genre
cursor.execute("""
    SELECT
        title,
        genre,
        price
    FROM books b1
    WHERE price > (
        SELECT AVG(price)
        FROM books b2
        WHERE b2.genre = b1.genre
    )
    ORDER BY genre, price DESC
""")

above_genre_avg = cursor.fetchall()
print("Books above average price for their genre:")
for title, genre, price in above_genre_avg:
    print(f"  {title} ({genre}): ${price:.2f}")

conn.close()

🔑 Key Takeaways

• **Subqueries** allow nesting queries for complex filtering and data retrieval

• **UNION/UNION ALL** combine results from multiple SELECT statements

• **HAVING** filters grouped results (works after GROUP BY)

• **Window functions** perform row-by-row calculations across result sets

• **CTEs (WITH clause)** improve readability and allow reusable query blocks

• **Correlated subqueries** reference outer query columns for dynamic filtering

• Complex queries should be broken down into readable, testable parts

📚 Further Learning

Master advanced database patterns:

• [Advanced Python SQLite Patterns](/courses/python/database_basics/advanced/sqlite_with_python) - Connection pooling and async patterns

• [Performance Optimization](/courses/python/database_basics/advanced/database_performance) - Query profiling and index design

• [Challenges & Quizzes](/#) - Test your advanced querying skills