Chapter 11: Analytical Queries in SQL

Accounting Analytics: An Integrated Approach

In this chapter

Part III of V: Accounting Analytics with SQL

Chapter 3 of 4 in this Part

• 11.1 Beyond Grouping
• 11.2 Classifying Rows with CASE
• 11.3 Working with Dates
• 11.4 Turning Rows into Columns with Conditional Aggregation

• Guided Tutorial 11.1
• 11.6 Window Functions
• Guided Tutorial 11.2
• 11.8 Common Table Expressions
• 11.9 Saving an Analysis as a View
• Guided Tutorial 11.3
• 11.11 Looking Ahead

This chapter builds the monthly measure the controller needs before she takes Chapter 10’s finding to the plant manager. Each module follows a section of the chapter, and the three guided tutorials extend one script, Chapter11.sql, ending with a saved view.

Learning objectives

1. Classify rows with CASE expressions, and turn values into columns with conditional aggregation.
2. Use SQLite’s date functions to group transactions by period, find period ends, and measure the time between two dates.
3. Apply window functions, with partitions, frames, ranking functions, and offset functions such as LAG, to compare rows and periods without collapsing the detail.

The first three objectives cover CASE and conditional aggregation, the date functions, and window functions.

Learning objectives (continued)

4. Organize a multi-step analysis as a chain of common table expressions that can be read and checked one step at a time.
5. Save a repeatable analysis as a view in a working copy of the database, and query it like a table.
6. Combine these techniques into a monthly performance measure, cut off at the last day with time records, that management can track.

The last three cover common table expressions, views, and the monthly measure that combines them.

When did it start, and is it still growing?

• Output was flat, but the plant recorded far more hours than its standards allow
• Recorded direct hours stayed within the standard; indirect time and overtime grew
• The plant manager will ask: when did it start, and is it still getting worse?
• How much is overtime, and is it spread across the plant or concentrated?

Annual totals cannot answer those questions. The controller needs a measure she can follow month by month, one that makes sense in months when production is lumpy, saved in the database so it runs again next month.

11.1 Beyond Grouping

Four features answer what grouping cannot

• CASE places a row in a category that no column records
• Date functions turn dates into periods and measure the time between them
• Window functions calculate across rows without collapsing them
• Common table expressions and views break a long analysis into named, saved steps

Grouping summarizes rows, but some questions need categories as columns, comparisons with other rows such as the same month last year, totals that accumulate while every month stays visible, or so many steps that nested subqueries become hard to check.

11.2 Classifying Rows with CASE

CASE classifies rows that no column records

• The searched form tests conditions in order; the first true one wins
• Without an ELSE, a row that meets no condition gets NULL, so write one
• The simple form compares one column with values, so WHEN NULL never matches
• ORDER BY CASE sorts in an order no column holds, such as statement order

CASE works like Excel’s IFS from Chapter 5, and it can appear anywhere a value can: in SELECT, WHERE, GROUP BY and ORDER BY. The chapter’s example classifies each payroll line as hourly pay, salaries, employer taxes and benefits, or employee deductions; a broad condition placed first would hide the narrower ones below it.

11.3 Working with Dates

SQLite’s date functions

Table 11.1 · SQLite date functions, for the date d of 2026-03-06

Function	Returns	Example result
strftime('%Y', d)	The year, as text	2026
strftime('%m', d)	The month, as two digits	03
strftime('%Y-%m', d)	The year and month	2026-03
strftime('%w', d)	The weekday, 0 for Sunday to 6 for Saturday	5
date(d)	The date without its time	2026-03-06
date(d, 'start of month')	The first day of the month	2026-03-01
date(d, '+1 month', '-1 day')	A date moved by the modifiers, in order	2026-04-05
julianday(d)	The date as a number of days	2461105.5

The Charles River dates are text in year-month-day form; strftime and date read and return text, and julianday returns a number. Modifiers such as ‘start of month’ and ‘+1 month’ are applied in order, from left to right.

Month ends and the days between dates

• date(d, 'start of month', '+1 month', '-1 day') is the month’s last day
• So a posting date equal to it is a month-end entry, mostly accruals and depreciation
• The difference of two julianday values is the number of days between them
• Here the fiscal year is the calendar year, so strftime and FiscalPeriod agree

The julianday difference is the SQL counterpart of subtracting one date from another in Excel. A company whose fiscal year starts in another month needs a mapping from calendar months to fiscal periods.

Watch out: date logic is easy to get subtly wrong

• Modifier order matters: adding a month to the 31st can land in the month after next
• Apply ‘start of month’ first whenever you step from month to month
• strftime and date return text; julianday returns a number. Compare results with the matching type
• Other systems name these functions differently, so date logic travels least well

strftime returns text, and a function’s result is not a column, so it has no column affinity to convert the number with: strftime('%Y', PostingDate) = 2026 finds no rows. Compare with ‘2026’, or CAST the result to INTEGER. A value’s type and an expression’s conversion rules are different ideas. SQL Server, for example, uses YEAR, DATEADD and DATEDIFF.

11.4 Turning Rows into Columns with Conditional Aggregation

Conditional aggregation turns rows into columns

Figure 11.1 · Conditional Aggregation Turns Rows into Columns

Each CASE passes the amount of one source’s rows and zero for the others, so each SUM collects one source into its own column. Rows that meet no condition, here the work order closes, add zero to every column.

A CASE inside SUM is the SQL form of SUMIFS

• The query names its columns, so it suits categories you know in advance
• COUNT(CASE WHEN … THEN 1 END) counts rows, because COUNT skips the NULLs
• SUM(CASE WHEN … THEN 1 ELSE 0 END) gives the same count as a 1-or-0 flag
• A PivotTable, by contrast, makes a column for every value it finds

CASE is the SQL form of IFS, and a CASE inside SUM the SQL form of SUMIFS. The difference from Excel is where the result lives: one piece of text produces every row and column at once, and it runs unchanged on next year’s data.

Check your understanding

Which tool does each task?

1. Label each payroll line as pay, salary, employer cost or deduction
2. Group transactions by month
3. Find the last day of each month
4. Count the days from a work order’s release to its completion

Ask students which of the four would need care with the order of its parts.

Each task has its tool

1. A searched CASE, with an ELSE
2. strftime('%Y-%m', d) in GROUP BY
3. date with ‘start of month’, ‘+1 month’ and ‘-1 day’, in that order
4. The difference of two julianday values

The third is the one where order matters: start of month comes first, or a 31st can carry into the month after next.

Guided Tutorial 11.1: Classifying the Payroll and Measuring the Hours by Month

Classify and measure in five steps

1. Classify the manufacturing payroll, and tie it to the ledger
2. Pivot the labor time by month
3. Find how far the time records fall short of the month’s end
4. Compare the overtime share of each kind of labor
5. Measure how long work orders take

Students classify the manufacturing payroll and tie it to the ledger, pivot the labor time by month with conditional aggregation, find where the time records stop, compare overtime shares, and measure how long work orders take.

Indirect time almost vanishes for four months

Figure 11.2 · Manufacturing Labor Time in the First Months of 2024 (detail)

The start-up month has more indirect than direct hours, and then indirect time almost disappears from February to May of the first year before becoming a large part of every month from the summer. The query groups the labor time by month and puts direct, indirect and overtime hours in columns of their own.

Direct overtime rose; indirect overtime stayed flat

Figure 11.3 · Overtime Shares by Kind of Labor and Month (detail)

The overtime share of direct labor is a quarter or more in most months of the latest year, up from about a tenth in the first half of the first year. The share of indirect labor stays at about a twelfth throughout.

Checkpoint

Check that:

• the payroll categories other than employee deductions add up to the ledger’s charge to account 1090 for fiscal 2026;
• the monthly pivot has one row for each of the 36 months, with far less indirect time from February to May 2024 than later in the year, and few days of time records in December 2026;
• the time records end nearly three weeks before the end of December 2026;

The payroll categories other than deductions tie to the ledger’s charge to the clearing account. The time records stop nearly three weeks before the year ends, which the measure in Tutorial 11.3 must allow for.

Checkpoint (continued)

• the overtime share of direct labor rose from about a tenth to a quarter or more, while that of indirect labor stayed at about a twelfth;
• the average time from release to completion more than doubled from fiscal 2024 to fiscal 2026;
• Chapter11.sql holds the header and the queries of this tutorial, each under a comment.

CASE, conditional aggregation and the date functions have given three views of the same change: indirect time that becomes a large part of the records from the middle of the first year, more overtime on direct labor, and work orders that take longer to finish.

11.6 Window Functions

A window calculates across rows and keeps them

• An aggregate with GROUP BY collapses rows; a window function keeps every row
• OVER () looks at every row; PARTITION BY at the rows that share its columns
• GROUP BY decides the rows of the result; PARTITION BY what each calculation sees
• ORDER BY inside OVER turns a SUM into a running total

SUM(Gap) OVER (PARTITION BY FiscalYear ORDER BY FiscalPeriod) is a year-to-date total that restarts each fiscal year. SQLite has had window functions since version 3.25, and DB Browser 3.13.1 includes a much later version.

Watch out: the default window includes ties

• With ORDER BY, the default window runs through the current row’s peers
• Peers are the rows tied with it on the ORDER BY columns
• Tied rows all show the total through the last of them
• Stop the window at the current row with ROWS UNBOUNDED PRECEDING

Each fiscal period appears once in each year, so a year-to-date total by period has no ties. A running total of postings ordered by their date would have many.

Ranking functions number the rows

• ROW_NUMBER gives every row its own number, even when rows tie
• RANK gives ties one number and skips the numbers after them
• DENSE_RANK gives ties one number without skipping
• For a stable numbering, add a unique column to the window’s ORDER BY

Ranking the Furniture items by standard labor hours per unit shows the first ties in the seventh to tenth rows. ROW_NUMBER numbers tied items in whatever order it meets them, which need not be the order of the result.

A partition groups the rows; a frame slides

Figure 11.4 · Partitions and Frames in a Window Function

The year-to-date total restarts in January because each fiscal year is its own partition. The outlined frame, the current month and the two before it, slides down the column to give a moving three-month total. That moving total has no year partition, so in January it reaches back into December: the figure’s January 2026 row adds November and December 2025.

Frames, named windows and offsets

• ROWS BETWEEN 11 PRECEDING AND CURRENT ROW is a trailing twelve months
• A ROWS frame counts rows: a missing month reaches one month further
• WINDOW w AS (...) names a window that several functions share
• LAG(x) partitioned by month: the latest earlier year with that month

Check that a query relying on a frame has one row for every month. COUNT over the window shows where it holds fewer than twelve rows, which are twelve months only when no month is missing. Of this chapter’s functions, a frame shapes only the aggregates. LAG, LEAD and the ranking functions read the whole partition. LAG partitioned by month returns the previous year only when no year is missing that month. An alias of the SELECT list cannot be used inside OVER, so the window repeats the expression; SQLite answers “no such column”.

The window functions of this chapter

Table 11.2 · Window functions used in this chapter

Function	Returns
ROW_NUMBER()	A sequence number within the partition: 1, 2, 3, even for ties
RANK(), DENSE_RANK()	A rank in which ties share a number; RANK skips numbers after a tie
SUM(x) OVER (...)	A total over the window: the whole partition, or a running or moving total
AVG, COUNT, MIN, MAX	The same aggregates over the window
LAG(x, n), LEAD(x, n)	The value of x n rows before, or after, the current row

The aggregates you know, SUM, AVG, COUNT, MIN and MAX, all work over a window. LAG and LEAD reach back or forward a given number of rows, and their second argument can be left out when it is one.

The clauses that define a window

Table 11.3 · Clauses that define a window

Clause	Effect
OVER ()	The window is every row of the result
PARTITION BY	Divides the rows into groups that the window never crosses
ORDER BY	Orders each partition; for an aggregate, the default window runs to the current row and its peers
ROWS BETWEEN n PRECEDING AND CURRENT ROW	The current row and the n rows before it
ROWS UNBOUNDED PRECEDING	Every row from the first of the partition to the current one
WINDOW w AS (...)	Names a window, for use as OVER w

These clauses combine: a partition, an order within it, and a frame of the ordered rows. The named window keeps a long query from repeating the same definition.

Windows are calculated with SELECT

• After WHERE, GROUP BY and HAVING; before DISTINCT, ORDER BY and LIMIT
• So a window can work on grouped totals: a running total of monthly sums
• But WHERE and HAVING cannot filter on its result (“misuse of …”)
• To keep the top-ranked rows, filter the window’s result in an outer query

Trailing twelve-month figures and year-over-year comparisons are standard in management reporting because they separate a trend from one month’s swings; the price is delay, so the monthly and trailing figures are read together. Window functions are the SQL form of Chapter 6’s Show Values As.

Check your understanding

Which window does each question need?

1. A year-to-date total that restarts each year
2. A trailing twelve-month total
3. Each month against the same month a year earlier
4. The three largest months of each year

Ask students to name the partition, the order and the frame for each.

Each question has its window

1. SUM partitioned by year, ordered by month
2. SUM with ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
3. LAG partitioned by month, ordered by year
4. RANK partitioned by year, filtered in an outer query

The fourth needs two levels because WHERE runs before the window is calculated.

Guided Tutorial 11.2: Tracking the Gap Month by Month

Track the gap in seven steps

1. Calculate the monthly gap and its year-to-date total
2. Compare each month with the same month a year earlier
3. Rank the months within each year
4. Count the pay dates of the largest months
5. Count the pay dates of every month
6. Rank the work centers by the hours recorded against their plan
7. Add the plant’s total

Students calculate the monthly gap in the clearing account with a year-to-date total, compare each month with the same month a year earlier, rank the months, count their pay dates, and rank the work centers by hours recorded against their plan.

The year-to-date gap grew in each year

Figure 11.5 · The Monthly Gap in Account 1090 and Its Year-to-Date Total (detail)

The gap is the actual cost charged to the clearing account less the standard cost released by completions, without the work order closes. The year-to-date total restarts each January, and its December figure grew in each year.

The largest months have three pay dates

Figure 11.6 · The Largest Months of the Gap and Their Pay Dates (detail)

The largest month of each year has three pay dates, because payroll is recorded on the pay date with no month-end accrual. The first year also has two pay dates fewer than the later years, part of why its gap was smaller.

Packing and QA record far more than planned

Figure 11.7 · Work Centers Ranked by Hours Recorded Against Plan

Packing and Quality Assurance recorded several times their planned hours, while Assembly, Cutting and Finishing recorded well under theirs. The plant as a whole recorded fewer direct hours than planned, so the totals hide the concentration.

Checkpoint

Check that:

• the year-to-date gap restarts each January, and its December figure grew in each year;
• the same-month comparison is NULL for fiscal 2024 and shows larger gaps in most months of fiscal 2026, with July and August 2026 far from the rest;
• the ranking query returns the three largest months of each year, filtered in the outer query;

The same-month comparison is NULL in the first year, because it has no earlier year. The ranking query filters its ranks in an outer query, as the window module required.

Checkpoint (continued)

• the largest month of each year has three pay dates, each year has two such months, and fiscal 2024 has two pay dates fewer than the later years;
• Packing and Quality Assurance record several times their planned hours, while the plant as a whole records fewer direct hours than planned;
• Chapter11.sql holds this tutorial’s queries under their comments.

The gap grew from year to year, and its largest months come from the pay calendar, not from a few bad months. The pattern in the time records by work center is one the controller cannot explain from the numbers alone.

11.8 Common Table Expressions

A CTE names each step of a long query

• WITH name AS (…) defines a named subquery at the start of a query
• Several CTEs, separated by commas, can each use the ones before them
• The query reads from top to bottom, not from the inside out
• Check each step by selecting from it on its own

The chapter’s example totals the monthly gap in one CTE, and the outer query adds the year-to-date total. Each step’s name says what it produces, which makes a long analysis reviewable.

Parameters, and CTE or derived table

• A one-row CTE can hold a parameter, such as an as-of date
• A scalar subquery reads it wherever it is needed, so one change updates the query
• SQLite may calculate a CTE once or treat it as a derived table
• So choose for readability, and time the query if its speed matters

The SQLite documentation describes a CTE as working like a view that exists for the length of one statement. Tutorial 11.3 chains four CTEs into the monthly measure.

11.9 Saving an Analysis as a View

A view saves the query, not the rows

DROP VIEW IF EXISTS MonthlyLaborEfficiency;
CREATE VIEW MonthlyLaborEfficiency AS
WITH ...
SELECT ...;

• Every query of the view runs the saved query on the current data
• Leave ORDER BY out: the query that uses the view sorts it

A view cannot be changed in place, so a script drops it first and can be run again. Every user gets the same logic, which makes a view a control as well as a convenience, and its result can go to Excel or Power BI with Copy with Headers or Export to CSV.

The view lives in the working copy

• It is the one thing in this Part you write into the database
• CREATE VIEW takes effect at once, but only Write Changes saves it to the file
• Closing first and clicking Discard removes it
• CREATE TEMP VIEW lasts one session and works even read-only

That is why Chapter 9 made a working copy: the view lives in CharlesRiver_Work.sqlite, and the downloaded file is never changed. A chain of CTEs is the SQL form of Power Query’s Applied Steps, and a view the counterpart of a refreshable query.

Watch out: a view can break when its tables change

• A view depends on the tables and columns it uses
• Remove or change one, and the view can fail or change its meaning
• SQLite may refuse the change, or the view fails when next queried
• Document every view: purpose, source tables, and the checks its results pass

SQLite refuses to drop a column that a view uses, while a dropped table leaves the view failing the next time it is queried. SQLite carries a renamed table or column into the views that use it, but test a view again after any change to its tables. Document a view in its script just as you document any other query.

Check your understanding

What happens in each case?

1. You close the database without clicking Write Changes
2. A column the view uses is removed
3. You put ORDER BY inside the view
4. You need the view only for this session

Ask students which of the four would surprise someone else who uses the view.

Each case has its consequence

1. DB Browser asks; Discard removes the view
2. SQLite refuses to drop it while the view uses it
3. Leave it out: the query using the view sorts
4. Use CREATE TEMP VIEW

The second is the reason for documenting a view’s source tables and checks. A dropped table, by contrast, shows up only when the view is next queried.

Guided Tutorial 11.3: Building the Monthly Measure and Saving It as a View

Build and save the measure in nine steps

1. Build the output step and check it
2. Build the hours step and check it
3. Join the two steps, and check the join
4. Add the trailing twelve months
5. Look at the months before the window is full
6. Save the measure as a view
7. Find the view, and write it to the file
8. Query the view
9. Complete and save the script

Students build the output step and the hours step, join them, add the trailing twelve months with a named window, look at the months before the window is full, save the chain as a view, write it to the file, and query it.

The chain of CTEs in the monthly measure

Figure 11.8 · The Chain of Common Table Expressions in the Monthly Measure

Output counts standard hours only up to the last day with time records, so the output does not run on after the hours stop, although indirect time ends a week earlier. The Trailing step calculates the trailing ratios over the named window, and the final SELECT keeps the months whose window holds twelve monthly rows.

The saved view in the database

Figure 11.9 · The Saved View in the Database Structure Tab

CREATE VIEW makes the view appear under Views on the Database Structure tab, with its six columns. Write Changes then saves it in the working-copy file.

The measure for the year, queried from the view

Figure 11.10 · The Monthly Measure for Fiscal 2026, Queried from the View

The monthly ratio swings from month to month, while the trailing figures change slowly. Each trailing figure pools twelve months’ hours and standard hours, and the December row covers only the days to the last time record, with its indirect time ending a week before its direct time, so it is provisional. Counted to the last day of indirect time on both sides, its total is a little lower. Indirect hours per standard hour have levelled off, while the trailing direct figure creeps up and the trailing total peaks in the tenth month.

Checkpoint

Check that:

• each step of the chain returns 36 months, including the join of the two summaries, and the view keeps the 25 months with full windows;
• the months before December 2024 have partial windows, led by the start-up month of January 2024;
• the trailing indirect hours per standard hour level off from the middle of fiscal 2025, while the trailing direct figure creeps up through fiscal 2026;
• the trailing total reaches its highest point in October 2026, and each December’s figures equal the ratios of Tutorial 10.3;

The partial windows before the twelfth month, led by the start-up month, are why the final step keeps only full windows. Each December’s trailing figures equal the annual ratios of Tutorial 10.3.

Checkpoint (continued)

• the view MonthlyLaborEfficiency appears under Views on the Database Structure tab of your working copy, and Write Changes has saved it;
• the query of the view for fiscal 2026 returns the same values as the full query;
• Chapter11.sql is saved with a completed header and a comment above each query.

The controller now has a measure smoothed enough to show a trend, split into the kinds of labor that make it up, and saved so that it runs again every month. Its parts are recorded hours. Activity, staffing, how time is classified and how complete the records are need checking before they are read as time away from the work orders.

11.11 Looking Ahead

Back to the plant manager’s questions

• When: in the records, mid-way through the first year, as indirect time and overtime grow
• Still worse? Indirect time has been flat since mid-way through the second year; direct time crept up
• Overtime: about a fifth of all hours, and more than a quarter of direct hours
• Where: Packing and QA record several times their plan; other centers record less

The trailing measure reached its highest point late in the latest year. The audit committee will ask whether the records behind the explanation are reliable, including why indirect time almost vanishes for four months, which the internal auditors take up in Chapter 12.

Summary

• CASE classifies rows; a CASE inside an aggregate turns categories into columns
• strftime groups dates into periods; date finds period ends; julianday differences count elapsed days
• Window functions rank, accumulate and compare rows while keeping them
• CTEs name the steps of a long query; a view saves it for every month’s run
• The monthly measure combines them, cut off at the last day with time records

This was diagnostic analytics, tracing when and where the extra hours arose. The trailing measure is also a predictive baseline: each new month can be judged against it, and Chapter 7’s forecasting methods could project it.

Key terms

CASE expression · Common table expression (CTE) · Conditional aggregation · Date function · Frame · LAG and LEAD · Named window · Partition · Ranking function · Running total · Trailing twelve months · View · Window function

Every term is defined at the end of the chapter. Partition, frame and trailing twelve months are the ideas management reports rely on most.

Practice and next steps

• Financial Accounting
  • Exercise 11.1: The Financial Statements from the Ledger
  • Exercise 11.2: Monthly Revenue with Year-to-Date and Prior-Year Comparisons
• Managerial Accounting
  • Exercise 11.3: Ranking Items by Unit Margin Within Each Product Line
  • Exercise 11.4: Is Overtime Concentrated?
• Auditing
  • Exercise 11.5: Journal Entry Tests in SQL
  • Exercise 11.6: Requisitions Just Under the Approval Threshold

Next: Chapter 12: Audit Analytics in SQL

Assign one exercise from each perspective; Exercise 11.5 repeats Chapter 8’s journal entry tests in SQL, and Exercise 11.1 builds the financial statements from the ledger. Chapter 12 turns to audit analytics in SQL.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/11-analytical-queries/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.