Aggregate Functions - GROUP BY, HAVING, Window Functions

▶

Steps

setup

Start Sequential Scan on sales Table

The query engine begins by scanning all rows in the sales table sequentially to read raw data for processing.

💡 Sequential scan reads every row because no index is used; this is the first step to access data.

Line:

cursor.execute('''CREATE TABLE sales (product_id TEXT, category TEXT, sales_amount REAL, sale_date TEXT)''')

💡 Understanding that the scan operator reads raw data is fundamental to following subsequent aggregation steps.

traverse

Read first row from sales

The scan operator reads the first row: product_id P1, category Electronics, sales_amount 100.

💡 Reading rows one by one is how the engine feeds data into aggregation.

Line:

cursor.executemany('INSERT INTO sales VALUES (?, ?, ?, ?)', sales_data)

💡 Each row read will contribute to aggregation sums.

traverse

Read second row from sales

The scan operator reads the second row: product_id P2, category Electronics, sales_amount 150.

💡 Continuing to read all rows ensures complete data for aggregation.

Line:

cursor.executemany('INSERT INTO sales VALUES (?, ?, ?, ?)', sales_data)

💡 Each new row updates aggregation groups or creates new ones.

traverse

Read third row from sales

The scan operator reads the third row: product_id P3, category Books, sales_amount 200.

💡 Reading all categories and products is necessary for correct grouping.

Line:

cursor.executemany('INSERT INTO sales VALUES (?, ?, ?, ?)', sales_data)

💡 New categories and products will form new aggregation groups.

traverse

Read fourth row from sales

The scan operator reads the fourth row: product_id P4, category Books, sales_amount 50.

💡 Completing the scan ensures all data is available for aggregation.

Line:

cursor.executemany('INSERT INTO sales VALUES (?, ?, ?, ?)', sales_data)

💡 All rows are now ready for aggregation processing.

fill_row

Start Aggregation: Group by category and product_id

The aggregate operator begins grouping rows by category and product_id, summing sales_amount for each group.

💡 Grouping is essential to compute total sales per product within each category.

Line:

SELECT category, product_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY category, product_id

💡 Aggregation reduces multiple rows per product into a single summary row.

fill_cells

Aggregate first group: Electronics, P1

Aggregate operator sums sales_amount for product P1 in Electronics category: 100.

💡 Each group accumulates sales to compute total_sales.

Line:

SUM(sales_amount) for group (Electronics, P1)

💡 Aggregation creates a summary row per product.

fill_cells

Aggregate second group: Electronics, P2

Aggregate operator sums sales_amount for product P2 in Electronics category: 150.

💡 Each product's total sales are computed separately.

Line:

SUM(sales_amount) for group (Electronics, P2)

💡 Aggregation distinguishes products within the same category.

fill_cells

Aggregate third group: Books, P3

Aggregate operator sums sales_amount for product P3 in Books category: 200.

💡 Aggregation handles multiple categories independently.

Line:

SUM(sales_amount) for group (Books, P3)

💡 Grouping by category and product creates distinct summary rows.

fill_cells

Aggregate fourth group: Books, P4

Aggregate operator sums sales_amount for product P4 in Books category: 50.

💡 Completing aggregation for all products prepares data for ranking.

Line:

SUM(sales_amount) for group (Books, P4)

💡 All product totals are now computed.

expand

Start Window Function: RANK() OVER (PARTITION BY category ORDER BY total_sales DESC)

The window operator begins assigning ranks to products within each category based on total_sales descending.

💡 Window functions compute rankings without collapsing rows, preserving detail.

Line:

RANK() OVER (PARTITION BY category ORDER BY total_sales DESC)

💡 Ranking orders products by sales within categories.

expand

Assign rank 1 to highest sales in Books category

The window function assigns rank 1 to product P3 with total_sales 200 in Books category.

💡 Highest sales product in a category gets rank 1.

Line:

RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank

💡 Ranking reflects ordering within partitions.

expand

Assign rank 2 to second highest sales in Books category

The window function assigns rank 2 to product P4 with total_sales 50 in Books category.

💡 Ranking increments for next lower sales within same category.

Line:

RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank

💡 Ranking orders all products within partitions distinctly.

expand

Assign rank 1 to highest sales in Electronics category

The window function assigns rank 1 to product P2 with total_sales 150 in Electronics category.

💡 Ranking restarts at 1 for each category partition.

Line:

RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank

💡 Partitioning resets ranking per category.

expand

Assign rank 2 to second highest sales in Electronics category

The window function assigns rank 2 to product P1 with total_sales 100 in Electronics category.

💡 Ranking continues incrementally within category partition.

Line:

RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank

💡 Ranking orders all products distinctly within categories.

sort

Sort final output by category and rank

The sort operator orders the final rows by category ascending and rank ascending for output presentation.

💡 Sorting ensures output is grouped by category and ranked order.

Line:

ORDER BY category, rank

💡 Final output is user-friendly and logically ordered.

reconstruct

Output final sorted rows

The sorted rows are output in order: Books P3 rank 1, Books P4 rank 2, Electronics P2 rank 1, Electronics P1 rank 2.

💡 Final output matches expected ranking and grouping.

Line:

cursor.execute(query)
results = cursor.fetchall()

💡 The query plan successfully produces the desired ranked output.

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# STEP 1: Create sales table
cursor.execute('''CREATE TABLE sales (product_id TEXT, category TEXT, sales_amount REAL, sale_date TEXT)''')

sales_data = [
    ('P1', 'Electronics', 100, '2023-01-01'),  # STEP 2-5: Insert rows
    ('P2', 'Electronics', 150, '2023-01-01'),
    ('P3', 'Books', 200, '2023-01-01'),
    ('P4', 'Books', 50, '2023-01-03')
]
cursor.executemany('INSERT INTO sales VALUES (?, ?, ?, ?)', sales_data)
conn.commit()

# STEP 6-10: Execute query with aggregation and window function
query = '''
SELECT category, product_id, total_sales, 
       RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank
FROM (
    SELECT category, product_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY category, product_id
) sub
ORDER BY category, rank
'''
cursor.execute(query)  # STEP 11-17: Execute and fetch results
results = cursor.fetchall()

print('Ranking products within each category:')
for row in results:
    print(row)

conn.close()

📊

Aggregate Functions - GROUP BY, HAVING, Window Functions - Watch the Algorithm Execute, Step by Step

Watching the query plan step-by-step reveals how SQL engines optimize and execute complex queries involving grouping, filtering, and window functions, which is hard to grasp from code alone.

Sequential scan operator is reading rows from sales table.

SeqScansalesrows≈4cost=10

Aggregaterows≈4

Windowrows≈4

Sortrows≈4

Rows processed: 0

No index available, so full table scan is necessary.

Sequential scan reads first row: P1, Electronics, 100.

SeqScansalesrows≈4cost=10

Aggregaterows≈4

Windowrows≈4

Sortrows≈4

Rows processed: 1

Row-by-row reading feeds aggregation.

Sequential scan reads second row: P2, Electronics, 150.

SeqScansalesrows≈4cost=10

Aggregaterows≈4

Windowrows≈4

Sortrows≈4

Rows processed: 2

Rows continue to flow to aggregation.

Sequential scan reads third row: P3, Books, 200.

SeqScansalesrows≈4cost=10

Aggregaterows≈4

Windowrows≈4

Sortrows≈4

Rows processed: 3

Rows continue to flow to aggregation.

Sequential scan reads fourth row: P4, Books, 50. Scan complete.

SeqScansalesrows≈4cost=10

Aggregaterows≈4

Windowrows≈4

Sortrows≈4

Rows processed: 4

All rows read; ready for aggregation.

Aggregate operator groups rows by category and product_id, summing sales_amount.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4

Sortrows≈4

Rows processed: 0

Aggregation uses hash or sort-based grouping.

Aggregate sums sales_amount for Electronics, P1: 100.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4

Sortrows≈4

Rows processed: 1

Aggregation output row created.

Aggregate sums sales_amount for Electronics, P2: 150.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4

Sortrows≈4

Rows processed: 2

Aggregation output row created.

Aggregate sums sales_amount for Books, P3: 200.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4

Sortrows≈4

Rows processed: 3

Aggregation output row created.

Aggregate sums sales_amount for Books, P4: 50. Aggregation complete.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4

Rows processed: 4

Aggregation output ready for window function.

Window operator starts ranking products within each category by total_sales descending.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4

Rows processed: 0

Window function computes rank without grouping.

Window function assigns rank 1 to P3 in Books category (200 sales).

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4

Rows processed: 1

Ranking uses partition and order.

Window function assigns rank 2 to P4 in Books category (50 sales).

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4

Rows processed: 2

Ranking continues within partition.

Window function assigns rank 1 to P2 in Electronics category (150 sales).

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4

Rows processed: 3

Ranking resets for new partition.

Window function assigns rank 2 to P1 in Electronics category (100 sales).

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4cost=5

Rows processed: 4

Ranking complete for all partitions.

Sort operator orders rows by category and rank.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4cost=5

Rows processed: 0

Sorting prepares final output order.

Final sorted rows output to user.

SeqScansalesrows≈4cost=10

Aggregaterows≈4cost=20

Windowrows≈4cost=30

Sortrows≈4cost=5

Rows processed: 4

Query execution complete.

Key Takeaways

✓ The query plan shows how raw data is first scanned, then grouped, ranked, and finally sorted for output.

This stepwise flow is hard to see from code but clear in the visualization.

✓ Window functions like RANK() compute rankings within partitions without collapsing rows, preserving detail.

Understanding window functions is easier when seeing how they operate after aggregation.

✓ Sorting at the end ensures the final output is logically ordered by category and rank for user consumption.

Sorting is a separate step that organizes results after all computations.

Practice

1. Trace the sequence of events when a transaction commits in a database that guarantees Durability. What happens immediately after the commit command?

easy

A. The transaction's changes are written to volatile memory and then acknowledged

B. The transaction's changes are visible to other transactions before being saved

C. The transaction's changes are flushed to stable storage before acknowledgment

D. The transaction's changes are rolled back if a system failure occurs immediately after commit

Solution

1. Step 1: Recall Durability definition

   Durability means once a transaction commits, its changes survive system failures.

2. Step 2: What ensures durability?

   Changes must be flushed to stable storage (disk) before acknowledging commit.

3. Step 3: Analyze options

   A is incorrect because volatile memory is not durable. B is about visibility, not durability. D contradicts durability.

4. Final Answer:

   Option C -> Option C

5. Quick Check:

   Durability requires stable storage flush before commit acknowledgment.

Hint: Durability = flush to disk before commit confirmation

Common Mistakes:

• Assuming volatile memory is enough for durability
• Confusing visibility with durability

2. In which scenario is MVCC most beneficial compared to traditional locking mechanisms in a database system?

easy

A. When strict serializability is required and no stale reads are allowed

B. When transactions are short-lived and mostly write-heavy with minimal reads

C. When many concurrent transactions perform mostly read operations with occasional writes

D. When the database workload consists primarily of batch updates with no concurrent reads

Solution

1. Step 1: Understand MVCC's strength in concurrency

   MVCC allows readers to access snapshot versions without blocking writers, making it ideal for workloads with many reads and fewer writes.

2. Step 2: Analyze write-heavy workloads

   Write-heavy workloads with minimal reads benefit less from MVCC since version maintenance overhead can be costly.

3. Step 3: Analyze batch updates without concurrent reads

   Batch updates without concurrent reads do not leverage MVCC's snapshot isolation benefits.

4. Step 4: Analyze strict serializability requirements

   MVCC provides snapshot isolation, which may allow some anomalies; strict serializability requires stronger mechanisms.

5. Final Answer:

   Option C -> Option C

6. Quick Check:

   MVCC shines in read-heavy concurrent environments [OK]

Hint: MVCC excels when many reads coexist with some writes

Common Mistakes:

• Assuming MVCC is best for write-heavy workloads
• Confusing snapshot isolation with strict serializability
• Believing MVCC is unnecessary without concurrent reads

3. Trace the sequence of lock acquisitions and releases when two transactions T1 and T2 both request shared locks on the same data item, followed by T1 requesting an exclusive lock on that item. What happens step-by-step?

easy

A. T1 acquires exclusive lock first; T2 waits for T1 to release before acquiring shared lock

B. T1 and T2 acquire shared locks simultaneously; T1 upgrades to exclusive lock immediately without waiting

C. T1 acquires shared lock; T2 waits until T1 releases its lock before acquiring shared lock

D. T1 and T2 acquire shared locks; T1 must wait to upgrade to exclusive lock until T2 releases its shared lock

Solution

1. Step 1: Both transactions request shared locks

   Shared locks are compatible, so T1 and T2 both acquire shared locks simultaneously.

2. Step 2: T1 requests exclusive lock (lock upgrade)

   Exclusive lock is incompatible with shared locks held by others, so T1 must wait until T2 releases its shared lock.

3. Step 3: T1 waits; T2 continues holding shared lock

   T1 cannot upgrade immediately; it must wait for T2 to release.

4. Final Answer:

   Option D -> Option D

5. Quick Check:

   Lock compatibility rules prevent immediate exclusive lock upgrade when others hold shared locks.

Hint: Exclusive lock upgrade waits for all shared locks to release [OK]

Common Mistakes:

• Assuming lock upgrade is immediate despite other shared locks
• Believing shared locks are exclusive
• Ignoring lock compatibility rules

4. What is a key trade-off or limitation of MVCC in terms of storage and performance?

medium

A. MVCC requires maintaining multiple versions, increasing storage and cleanup complexity

B. MVCC guarantees serializable isolation without any anomalies

C. MVCC eliminates all locking overhead, resulting in zero contention

D. MVCC reduces write amplification by avoiding version creation

Solution

1. Step 1: Understand MVCC version management

   MVCC stores multiple versions of rows to provide snapshot isolation, which increases storage usage and requires vacuuming or cleanup.

2. Step 2: Analyze locking overhead

   MVCC reduces locking but does not eliminate all contention, especially for writes.

3. Step 3: Analyze isolation guarantees

   MVCC typically provides snapshot isolation, which can allow anomalies; serializability requires additional mechanisms.

4. Step 4: Analyze write amplification

   MVCC increases write amplification due to version creation, not reduces it.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   MVCC trades off storage and cleanup overhead for concurrency benefits [OK]

Hint: MVCC trades storage and cleanup overhead for non-blocking reads

Common Mistakes:

• Assuming MVCC removes all contention
• Confusing snapshot isolation with serializability
• Believing MVCC reduces write amplification

5. What is a common trade-off or limitation of using strict Two-Phase Locking (2PL) in high-concurrency database systems?

medium

A. Strict 2PL can cause increased waiting and deadlocks, reducing concurrency

B. Strict 2PL eliminates deadlocks entirely, improving throughput

C. Strict 2PL allows transactions to release locks early, improving performance

D. Strict 2PL requires no lock compatibility checks, simplifying implementation

Solution

1. Step 1: Understand strict 2PL behavior

   Strict 2PL holds exclusive locks until commit, which can increase waiting times.

2. Step 2: Analyze deadlock potential

   Because locks are held longer, deadlocks can occur more frequently, reducing concurrency.

3. Step 3: Evaluate options

   B is incorrect; strict 2PL does not eliminate deadlocks. C is incorrect; locks are not released early. D is wrong; lock compatibility checks are essential. A correctly identifies the trade-off.

4. Final Answer:

   Option A -> Option A

5. Quick Check:

   Strict 2PL trades off concurrency for serializability, increasing deadlock risk.

Hint: Strict 2PL trades concurrency for serializability, risking deadlocks [OK]

Common Mistakes:

• Believing strict 2PL prevents deadlocks
• Thinking locks can be released early in strict 2PL
• Ignoring lock compatibility checks