Back to all blogs
    5 min read

    Advanced SQL & Transformations

    pysparkspark-sqladvanced-sql

    📊 Advanced Grouping (ROLLUP & CUBE)

    What Existed Previously: Traditionally, we used standard SQL GROUP BY to aggregate data (e.g., finding total sales per region).

    Problems Faced: If management wanted total sales per region, plus total sales per country, plus the grand total of everything in a single report, standard GROUP BY failed. You had to write multiple separate queries and clumsily glue them together with UNION. It was slow, inefficient, and the code was messy.

    How Present Technology Solves It: Spark SQL introduces ROLLUP and CUBE to generate multiple levels of subtotals and grand totals in a single, highly optimized scan of the data.

    1️⃣ ROLLUP (Hierarchical Aggregation)

    ROLLUP creates subtotals for a hierarchy (like Year -> Month). If you roll up by (Country, Region), it gives you:

    1. Sales by Country and Region
    2. Sales by Country (subtotal)
    3. Grand Total
    Analogy

    Think of a receipt from a supermarket. ROLLUP gives you the price of each item, the subtotal of all items, and the final grand total including tax at the very bottom.

    2️⃣ CUBE (Multi-dimensional Aggregation)

    CUBE goes a step further. It generates subtotals for every possible combination of the given columns. If you cube by (Country, Region), it gives you:

    1. Sales by Country and Region
    2. Sales by Country
    3. Sales by Region (ignoring country completely)
    4. Grand Total

    🔄 Pivoting Data

    What Existed Previously: Data is often stored in databases in a "long" format (many rows, few columns) because it's efficient for machines.

    Problems Faced: Humans are terrible at reading "long" data. We prefer "wide" data (like an Excel table with months as columns) to spot trends easily. Writing manual SQL to flip rows into columns is complex.

    How Present Technology Solves It: Spark provides the pivot() function to instantly flip rows into columns while summing up the data.

    python
    # Practical Example: Pivoting Data

data = [("2026-01", "Online", 500), 
        ("2026-01", "In-Store", 200),
        ("2026-02", "Online", 600)]

df = spark.createDataFrame(data, ["Month", "Channel", "Revenue"])

# Group by month, pivot the channels into columns, and sum revenue
pivoted_df = df.groupBy("Month").pivot("Channel").sum("Revenue")
pivoted_df.show()

# Expected Output:
# +-------+--------+------+
# |  Month|In-Store|Online|
# +-------+--------+------+
# |2026-01|     200|   500|
# |2026-02|    null|   600|
# +-------+--------+------+

    🧩 Common Table Expressions (CTEs)

    What Existed Previously: When queries got complicated, developers wrote nested subqueries (e.g., SELECT * FROM (SELECT * FROM (SELECT...))).

    Problems Faced: Nested subqueries create unreadable "spaghetti code." If another analyst tries to read it 6 months later, it takes hours to understand what the query is doing.

    How Present Technology Solves It: CTEs (using the WITH clause) allow you to define temporary, named result sets at the top of your query.

    sql
    -- Practical Example: Using a CTE for cleaner code
WITH RegionalSales AS (
    SELECT region, SUM(amount) as total_sales
    FROM sales
    GROUP BY region
)
SELECT region, total_sales 
FROM RegionalSales 
WHERE total_sales > 10000;
    Tip

    CTEs don't necessarily make the query run faster, but they dramatically improve readability and maintainability.

    ⚡ Materialized Views

    What Existed Previously: We used standard SQL "Views" (which are basically just saved SQL query text) to give users access to specific data.

    Problems Faced: If a complex dashboard queries a View that joins 5 massive tables, the database has to execute that heavy join every single time the dashboard is opened. This wastes massive compute resources and makes dashboards incredibly slow.

    How Present Technology Solves It: Materialized Views. Unlike a regular View, a Materialized View actually computes the result and saves the physical data to the disk.

    • Benefit: Querying a materialized view is instant because the heavy lifting is already done.
    • Trade-off: The data becomes "stale" as underlying tables update, so it must be periodically refreshed.

    🧪 Practice Drill

    text
    // Try answering these:
**Q1.** You need to generate a report showing sales by `Year`, `Month`, and the overall Grand Total. Which advanced grouping function should you use?

**Q2.** Your data team complains that your nested SQL query is impossible to read. What feature can you use to refactor it into clean, logical blocks?

**Q3.** What is the main difference between a regular View and a Materialized View?
    💡 Click for Solutions

    A1. ROLLUP. It is perfect for hierarchical data (Year -> Month) to generate subtotals and a grand total. CUBE would generate unnecessary combinations (like sales by Month ignoring Year).

    A2. CTE (Common Table Expression) using the WITH clause.

    A3. A regular View just saves the SQL query; every time you query it, the database re-runs the logic. A Materialized View actually executes the query and saves the resulting data to the disk, making subsequent reads nearly instantaneous.

    ← Spark SQL & DataFrames | Next Topic → Modern Data Architectures (Delta Lake & Iceberg)