Chapter 10 Exercises: Advanced SQL — Window Functions, OLAP Functions, Recursive Queries, and Temporal Queries

Instructions: These exercises progress from foundational window function usage through advanced analytical patterns. All queries should be written against the Meridian National Bank schema. Unless otherwise stated, write a single SQL statement for each problem.

Section A: Ranking Functions (Exercises 1-7)

Exercise 1 — Basic ROW_NUMBER

Write a query that assigns a sequential row number to every savings account (account_type = 'SAV'), ordered by current_balance descending. Return the row number, account_number, customer_name, and current_balance.

Exercise 2 — ROW_NUMBER for Pagination

Using a CTE with ROW_NUMBER, retrieve rows 21 through 40 (page 2, 20 rows per page) from the CUSTOMERS table, ordered alphabetically by customer_name. Return customer_id, customer_name, and signup_date.

Exercise 3 — ROW_NUMBER for Deduplication

The CUSTOMER_CONTACTS table has duplicate email addresses for some customers. Write a query that identifies the duplicates — for each distinct email address that appears more than once, keep only the row with the most recent last_verified_date and list all other rows (the ones that should be deleted). Return contact_id, customer_id, email, and last_verified_date.

Exercise 4 — RANK with Ties

Rank all branches by their total checking account balances using RANK(). Show branch_id, branch_name, total_checking_balance, and branch_rank. If two branches have identical totals, they should receive the same rank, and the next rank should be skipped.

Exercise 5 — DENSE_RANK

Repeat Exercise 4 using DENSE_RANK(). For branches with identical totals, the next rank should not be skipped. Explain in a SQL comment when you would prefer DENSE_RANK over RANK.

Exercise 6 — NTILE for Quartile Analysis

Divide all customers into 4 quartiles based on their total relationship balance (sum across all accounts). Return customer_id, customer_name, total_balance, and quartile. Which quartile contains the most customers, and why?

Exercise 7 — Combined Ranking

Write a single query that shows each account with all four ranking functions applied simultaneously (ROW_NUMBER, RANK, DENSE_RANK, NTILE(10)), ordered by current_balance DESC within each branch_id. Partition all four by branch_id. Explain in a SQL comment how the outputs differ for tied balances.

Section B: Aggregate Window Functions (Exercises 8-14)

Exercise 8 — Running Total

Write a query that shows a running total of deposit amounts (txn_type = 'D') for branch 101, ordered by txn_date and txn_id. Return txn_id, txn_date, txn_amount, and running_total. Ensure the running total increments one row at a time even when multiple transactions share the same date.

Exercise 9 — Partition Total as Reference Column

For every checking account, display the account_number, current_balance, the total balance across all checking accounts at the same branch (branch_total), and the account's balance as a percentage of the branch total (pct_of_branch). Order by branch, then by balance descending.

Exercise 10 — Moving Average

Calculate a 5-day moving average of daily transaction counts per branch. Use a CTE to first aggregate transactions to the daily level (branch_id, txn_date, daily_count), then apply the window function. Return branch_id, txn_date, daily_count, and moving_avg_5day.

Exercise 11 — Cumulative Count

For each customer, show every transaction with a cumulative transaction count that resets at the start of each calendar year. Return customer_id, txn_date, txn_amount, yearly_txn_number. Partition by customer_id and YEAR(txn_date).

Exercise 12 — MIN/MAX Window Functions

For each transaction, display the minimum and maximum transaction amounts within the same branch and month. Return txn_id, branch_id, txn_date, txn_amount, month_min, month_max, and a computed column range_position that shows where this transaction falls as a percentage between the month's min and max.

Exercise 13 — Difference from Group Average

Show each employee's salary alongside the average salary for their department. Add a column deviation that shows how much above or below the department average each employee is. Add a column deviation_pct showing this as a percentage. Order by department, then deviation descending.

Exercise 14 — Running Average vs. Overall Average

For branch 201 deposits in 2025, show the txn_date, txn_amount, the overall average deposit amount for the entire year (as a constant column), and a running average from the first transaction through the current row. At which point does the running average stabilize near the overall average?

Section C: Frame Specifications (Exercises 15-18)

Exercise 15 — ROWS vs. RANGE

Write two versions of a running total query for branch 101 deposits: - Version A: using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - Version B: using RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Order both by txn_date only (no tiebreaker). Explain in SQL comments why the results differ when multiple transactions share the same date.

Exercise 16 — Sliding Window Sum

Calculate a centered 5-transaction sliding window sum (2 preceding, current, 2 following) for each transaction at branch 101, ordered by txn_id. Return txn_id, txn_amount, window_sum, and window_count (using COUNT with the same frame to show how many rows are in each window — it will be less than 5 at the edges).

Exercise 17 — Year-to-Date with Partition Reset

Write a query that calculates a year-to-date running total of deposits per branch. The running total must reset at the start of each calendar year. Use PARTITION BY with both branch_id and YEAR(txn_date). Verify by checking that the first transaction of each year starts fresh.

Exercise 18 — ROWS BETWEEN for Trailing Periods

The risk team needs a trailing 90-day transaction volume for each customer. Since the data has gaps (not every customer transacts every day), explain in a SQL comment why ROWS BETWEEN 89 PRECEDING AND CURRENT ROW does not produce a true 90-day window. Then write a correct version using RANGE BETWEEN with date arithmetic, or a correlated approach, that handles gaps properly.

Section D: LAG, LEAD, and Value Functions (Exercises 19-23)

Exercise 19 — Month-Over-Month Change

Using LAG, calculate the month-over-month change in total deposits per branch. Return branch_id, yr, mo, total_deposits, prev_month_deposits, absolute_change, and pct_change. Handle the first month (no previous data) gracefully by showing NULL.

Exercise 20 — Days Between Transactions

For customer 10042, use LEAD to calculate the number of days until the next transaction. Return txn_date, txn_amount, next_txn_date, and days_gap. Flag any gap longer than 30 days with a column inactivity_alert set to 'ALERT'.

Exercise 21 — Year-Over-Year with LAG(12)

Using monthly aggregated data, compare each month's deposits to the same month in the prior year using LAG(total, 12). Return branch_id, yr, mo, monthly_deposits, same_month_prior_year, and yoy_pct_change.

Exercise 22 — FIRST_VALUE and LAST_VALUE

For each branch, show every account with the highest and lowest balance account numbers in that branch (using FIRST_VALUE and LAST_VALUE on account_number, ordered by current_balance). Remember to specify the correct frame for LAST_VALUE.

Exercise 23 — Trend Detection with LAG

Write a query that identifies customers whose monthly transaction volume has declined for 3 or more consecutive months. Use LAG to compare each month to the two preceding months. Flag only those with three consecutive declines.

Section E: Recursive CTEs (Exercises 24-29)

Exercise 24 — Basic Org Chart

Write a recursive CTE that displays the complete organizational hierarchy starting from the CEO (manager_id IS NULL). Include employee_id, employee_name, title, level, and indent the names by two spaces per level.

Exercise 25 — Direct and Indirect Report Count

Extend Exercise 24 to calculate, for each manager, the total number of direct and indirect reports. Use a recursive CTE to find all reports, then aggregate. The CEO should show the total employee count minus 1.

Exercise 26 — Management Chain Path

Write a recursive CTE that produces the full management chain for each employee as a delimited string. For example: CEO > VP Operations > Director > Manager > Employee. Order results by the path string.

Exercise 27 — Bill of Materials Explosion

Given the PRODUCT_COMPONENTS table, write a recursive CTE that explodes product 5001 ("Premium Account Package") into all its components at all levels. Track the cumulative quantity (multiply quantities through the hierarchy). Return product_name, level, unit_quantity, and total_quantity.

Exercise 28 — Referral Chain

Meridian tracks customer referrals in a REFERRALS table (referrer_customer_id, referred_customer_id, referral_date). Write a recursive CTE that, starting from customer 10001, finds all customers in the referral chain (customer 10001 referred 10015, who referred 10023, etc.). Include the chain depth and the path.

Exercise 29 — Depth-Limited Recursion with Cycle Detection

Modify Exercise 24 to include both a depth limit (maximum 8 levels) and cycle detection (prevent infinite loops if the data contains a cycle). Use a path-tracking column and LOCATE to detect cycles.

Section F: Temporal Tables (Exercises 30-34)

Exercise 30 — System-Time Table Setup

Write the DDL to create a system-time temporal table called ACCOUNT_TERMS with columns: account_id (INTEGER), interest_rate (DECIMAL(5,3)), monthly_fee (DECIMAL(7,2)), overdraft_limit (DECIMAL(10,2)), plus the required system-time columns. Create the history table and enable versioning.

Exercise 31 — System-Time Query

Assuming the CUSTOMER_PROFILES table from the chapter, write a query that retrieves customer 10042's profile as it existed on June 30, 2024. Then write a second query that returns all historical versions of that customer's profile, ordered by sys_start.

Exercise 32 — Business-Time Table Design

Write the DDL for a business-time temporal table called FEE_SCHEDULE that tracks fee amounts by fee type with non-overlapping business-time periods. Include a fee_type (VARCHAR(30)), fee_amount (DECIMAL(7,2)), and appropriate period columns. Use BUSINESS_TIME WITHOUT OVERLAPS in the primary key.

Exercise 33 — Business-Time Query

Write a query against FEE_SCHEDULE that returns the monthly maintenance fee (fee_type = 'MONTHLY_MAINT') that was effective on March 15, 2024. Then write a query that returns all fee changes for that fee type during 2024.

Exercise 34 — Bitemporal Scenario

Explain in SQL comments the difference between these two bitemporal queries, then write both:

Query A: "What do we currently believe the interest rate was for account 50001 on January 15, 2024?"

Query B: "What did we believe on March 1, 2024 that the interest rate was for account 50001 on January 15, 2024?"

When would Query A and Query B return different results?

Section G: OLAP Grouping Extensions (Exercises 35-38)

Exercise 35 — ROLLUP for Hierarchical Subtotals

Write a query using ROLLUP that shows total deposits by branch, year, and month — with subtotals at the branch+year level, the branch level, and a grand total. Use COALESCE or CASE with GROUPING() to label subtotal rows clearly.

Exercise 36 — CUBE for Cross-Tabulation

Write a query using CUBE on branch_id and account_type that shows total balances for every combination, including "all branches" and "all account types" summaries. Use the GROUPING() function to create a summary_level column that labels each row as 'DETAIL', 'BRANCH_TOTAL', 'TYPE_TOTAL', or 'GRAND_TOTAL'.

Exercise 37 — GROUPING SETS for Specific Combinations

The CFO wants exactly these groupings and no others: 1. Total deposits by branch and quarter 2. Total deposits by quarter only (all branches combined) 3. Grand total

Write a single query using GROUPING SETS that produces exactly these three levels.

Exercise 38 — ROLLUP Combined with Window Functions

Using ROLLUP to create branch-level and grand-total subtotals for monthly deposit amounts, then apply a RANK() window function to rank branches within each month by their deposit total. Only rank the detail rows (not the subtotal rows). Use a CTE to separate the ROLLUP from the ranking.

Section H: Comprehensive Analytical Challenges (Exercises 39-40)

Exercise 39 — Executive Dashboard Query

Write a single query (or CTE chain) that produces an executive dashboard row for each branch containing: - branch_id, branch_name - Total deposits this month, last month, and month-over-month change - Rank among all branches by this month's deposits - Number of active customers (at least 1 transaction this month) - Average account balance - The name of the branch's highest-balance customer

Use at least three different types of window functions in your solution.

Exercise 40 — Complete Customer Analytics Profile

For customer 10042, produce a comprehensive analytics profile using a multi-CTE query: 1. Monthly transaction summary with running totals and moving averages 2. Rank among all customers by total balance, by transaction volume, and by account count 3. Days since last transaction (using LEAD/LAG or DAYS function) 4. Product holding count vs. average customer product count (window function comparison) 5. Value quintile (NTILE)

Combine all of these into a single result set. This exercise demonstrates the power of combining CTEs with multiple window function types.

Answer Key Notes

Detailed solutions for exercises 1-20 are available in the instructor's companion guide. For exercises 21-40, solution outlines (approach description and key clauses) are provided.

Grading Rubric for Exercises 39-40: - Correct syntax and execution: 40% - Appropriate window function selection: 25% - Performance considerations (frame specifications, partition choices): 20% - Code clarity and CTE naming: 15%