11  Analytical Queries in SQL

Learning Objectives

After completing this chapter, you will be able to:

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.
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.

Chapter slides View slides Download PowerPoint

Opening Scenario

The controller has read your answer from Chapter 10. The plant produced about as much in fiscal 2026 as in fiscal 2024, but it recorded about half as many hours again as the standard hours of its output, up from somewhat more than the standard in 2024. The direct hours it recorded stayed within the standard, so the extra hours it recorded were indirect time, and overtime more than doubled. The cost of those hours was charged to account 1090 and cleared to the variance account when the work orders closed. She wants to take the finding to the plant manager, and she asks you, her staff accountant, to be ready for the first questions that will come back. When did it start? Is it still getting worse? How much of the extra time is overtime? Is it spread across the plant, or concentrated in a few places?

Annual totals cannot answer those questions. She needs a measure she can follow month by month, one that still makes sense in months when production is lumpy. It should also be saved in the database, so that it can be run again next month without rebuilding it. This chapter teaches the SQL features that make such a measure possible: classifying rows, working with dates, calculating across rows without losing them, and organizing a long query into steps that a reviewer can follow.

11.1 Beyond Grouping

The queries of Chapter 10 summarized rows into groups. Many analytical questions need more than that. Sometimes a row must be placed in a category that no column records, such as whether a payroll line is a cost of the company or a deduction from an employee’s pay. Sometimes the categories must become columns, so that direct and indirect hours sit side by side for each month. Some questions compare one row with others, such as this month with the same month last year, or ask for a total that accumulates through the year while every month stays visible. And some take so many steps that a single query, with subqueries nested inside subqueries, becomes hard to read and harder to check.

The four features of this chapter answer those needs. CASE expressions classify rows. 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 steps and save it for reuse.

11.2 Classifying Rows with CASE

A CASE expression returns a value chosen by conditions, much as the IFS function does in Excel (Chapter 5). The searched form lists conditions in order and returns the value of the first one that is true:

SELECT PayrollRegisterLineID, LineType, Amount,
    CASE
        WHEN LineType IN ('Regular Earnings', 'Overtime Earnings')
            THEN 'Hourly pay'
        WHEN LineType = 'Salary Earnings' THEN 'Salaries'
        WHEN LineType IN ('Employer Payroll Tax', 'Employer Benefits')
            THEN 'Employer taxes and benefits'
        ELSE 'Employee deductions'
    END AS Category
FROM PayrollRegisterLine;

CASE can appear anywhere a value can: in SELECT, in WHERE, in GROUP BY, and in ORDER BY. Three details matter. The conditions are tested in the order written, and the first true one wins, so a broad condition placed first hides the narrower ones below it. A CASE with no ELSE returns NULL for rows that meet no condition. In a classification that is rarely what you want, so write an ELSE even when you expect it never to be used; the exception, in the next section, is a CASE whose NULL is meant to be skipped. The simple form, CASE LineType WHEN 'Regular Earnings' THEN ... END, compares one expression with a list of values, and it is shorter when every condition is an equality on the same column. It compares with =, so WHEN NULL never matches, because a comparison with NULL is never true; test for NULL with the searched form, WHEN x IS NULL.

CASE is also the way to sort rows in an order that no column holds, such as the order of the financial statements. This query counts the accounts of each type and lists the assets first and the expenses last, where an alphabetical sort would put Equity before Liability:

SELECT AccountType, COUNT(*) AS Accounts
FROM Account
GROUP BY AccountType
ORDER BY CASE AccountType
    WHEN 'Asset' THEN 1 WHEN 'Liability' THEN 2 WHEN 'Equity' THEN 3
    WHEN 'Revenue' THEN 4 ELSE 5 END;

11.3 Working with Dates

The Charles River dates are text in the form year-month-day, as Chapter 9 showed, and times, where they exist, follow a space, as in 2024-01-06 10:00:00. SQLite’s date functions read text in that form, calculate with it, and return text or numbers (SQLite, n.d.-b). The date function returns a date moved by modifiers, text arguments such as 'start of month' or '+1 month' that follow the date and are applied in order, from left to right. Table 11.1 lists the functions you will use most.

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

strftime formats a date. Its first argument is a pattern in which each code beginning with % is replaced by a part of the date, so strftime('%Y-%m', WorkDate) gives the month of each work date, ready to group by. A common need in accounting is the last day of a month. date(d, 'start of month', '+1 month', '-1 day') moves to the first of d’s month, then to the first of the next month, and then back one day, so it returns the month’s last day whatever its length. The query below uses it to count the journal entries posted on the last day of a month:

SELECT EntryType, COUNT(*) AS Entries
FROM JournalEntry
WHERE PostingDate
    = date(PostingDate, 'start of month', '+1 month', '-1 day')
GROUP BY EntryType
ORDER BY Entries DESC;

Most of the entries it finds are accruals and depreciation, the entries that close each month. julianday converts a date to a count of days, so the difference between two julianday values is the number of days between the dates. It is the SQL counterpart of subtracting one date from another in Excel.

In this data, the fiscal year is the calendar year, and GLEntry’s FiscalPeriod is the calendar month, so strftime on a posting date and the FiscalYear and FiscalPeriod columns always agree. That is not true of every company, and a fiscal year that starts in another month needs a mapping from calendar months to fiscal periods.

WarningWatch out

The order of the modifiers matters. date('2026-01-31', '+1 month') returns 2026-03-03, because SQLite moves to February 31 and then carries the extra days into March, so apply 'start of month' first whenever you step from month to month. Of the functions in Table 11.1, strftime and date return text, and julianday returns a number. Chapter 9 showed that a quoted number is converted to the type of the column it is compared with, but a function’s result is not a column and has no such affinity, so nothing is converted: strftime('%Y', PostingDate) = 2026 compares text with a number and finds no rows. Compare with text, as in strftime('%Y', PostingDate) = '2026', or convert the result with CAST. Other database systems name these functions differently, for example YEAR, DATEADD, and DATEDIFF in Microsoft SQL Server, so date logic is the part of a query most likely to need rewriting in another system.

11.4 Turning Rows into Columns with Conditional Aggregation

A CASE expression inside an aggregate function turns categories into columns. Tutorial 10.2 listed the postings to account 1090 by year and source document type, one row for each combination. The query below puts the sources side by side instead, with one row for each year. Each CASE passes the amount of the rows of one source and zero for the others, so each sum collects one source:

SELECT FiscalYear,
    ROUND(SUM(CASE WHEN SourceDocumentType = 'PayrollSummary'
        THEN Debit ELSE 0 END), 2) AS Payroll,
    ROUND(SUM(CASE WHEN SourceDocumentType = 'JournalEntry'
        THEN Debit ELSE 0 END), 2) AS JournalEntries,
    ROUND(SUM(CASE WHEN SourceDocumentType = 'ProductionCompletion'
        THEN Credit ELSE 0 END), 2) AS Completions
FROM GLEntry
WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
GROUP BY FiscalYear
ORDER BY FiscalYear;

Figure 11.1 shows the rows of fiscal 2026 before and after. The technique is called conditional aggregation. Unlike a PivotTable, which creates a column for every value it finds, the query names its columns, so it suits categories you know in advance. The same idea counts rows. COUNT(CASE WHEN LaborType = 'Indirect Manufacturing' THEN 1 END) counts the indirect entries, because the CASE, with no ELSE, returns NULL for the other rows, and COUNT skips NULLs. SUM(CASE WHEN LaborType = 'Indirect Manufacturing' THEN 1 ELSE 0 END) gives the same count, and its CASE is the form to use when you want a flag of 1 or 0 on every row.

Figure 11.1: Conditional Aggregation Turns Rows into Columns.

NoteConnecting the Dots

In Chapter 5 you classified invoices by their payment terms with IFS, and in Chapter 6 you totaled revenue by category with SUMIFS and PivotTables. CASE is the SQL form of IFS, and a CASE inside SUM is the SQL form of SUMIFS: the condition decides which rows count, and the sum adds what passes. The difference is where the result lives. The Excel formulas sit in cells and are checked cell by cell. The query is one piece of text that produces every row and column at once, and it runs unchanged on next year’s data.

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

Context and objective. In this tutorial, you will classify the manufacturing payroll of fiscal 2026 with a CASE expression and tie the result to the ledger. You will then turn the manufacturing labor time into a month-by-month table of direct, indirect, and overtime hours, find how far the time records fall short of the year’s end, compare the overtime share of each kind of labor, and measure how long work orders took from release to completion in each year.

Prerequisites. Complete Chapter 10, and open CharlesRiver_Work.sqlite in DB Browser for SQLite on the Execute SQL tab. If Chapter10.sql is still open in the editor, click New tab (Ctrl+T) for an empty editor tab. Start a new script for the chapter with the header comment below, with your own name and the date, and click Save SQL file to save it as Chapter11.sql beside your working copy. As in Chapters 9 and 10, type each query of the chapter’s tutorials at the end of the script, under its comment, and run it by selecting it. If you start here, you need only the working copy, made as Step 1 of Tutorial 9.1 describes.

/* Chapter 11: the plant's hours, month by month
   Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
   Prepared by: your name, date
   Checks: listed at the end of Tutorial 11.3 */

Step-by-step instructions.

Step 1. Classify the manufacturing payroll, and tie it to the ledger. The payroll register lines record pay, the employer’s taxes and benefits, and the deductions withheld from employees’ pay. Only some of these are a cost to Charles River.

1. Type and run the query below.

   -- Tutorial 11.1: the manufacturing payroll of 2026 by category
   SELECT
       CASE
           WHEN prl.LineType IN ('Regular Earnings', 'Overtime Earnings')
               THEN 'Hourly pay'
           WHEN prl.LineType = 'Salary Earnings' THEN 'Salaries'
           WHEN prl.LineType IN ('Employer Payroll Tax', 'Employer Benefits')
               THEN 'Employer taxes and benefits'
           ELSE 'Employee deductions'
       END AS Category,
       ROUND(SUM(prl.Amount), 2) AS Amount
   FROM PayrollRegisterLine AS prl
       INNER JOIN PayrollRegister AS pr
           ON pr.PayrollRegisterID = prl.PayrollRegisterID
       INNER JOIN PayrollPeriod AS pp
           ON pp.PayrollPeriodID = pr.PayrollPeriodID
       INNER JOIN CostCenter AS cc ON cc.CostCenterID = pr.CostCenterID
   WHERE cc.CostCenterName = 'Manufacturing' AND pp.FiscalYear = 2026
   GROUP BY Category
   ORDER BY Amount DESC;

2. Type and run the query below, which totals the payroll that the ledger charged to account 1090 in fiscal 2026.

   -- Tutorial 11.1: the payroll charged to account 1090 in 2026
   SELECT ROUND(SUM(Debit), 2) AS PayrollCharged
   FROM GLEntry
   WHERE AccountID = 92 AND FiscalYear = 2026
       AND SourceDocumentType = 'PayrollSummary';

3. Add up the three categories other than employee deductions, and compare the sum with the ledger.

The three cost categories add up to the ledger’s charge to the cent. Employee deductions are left out, because they are withheld from pay that is already counted, not an additional cost. The CASE expression has turned seven line types into the four categories the accounting needs.

Step 2. Pivot the labor time by month. Conditional aggregation puts the hours of each kind of labor in a column of its own, and a count of the distinct work dates shows how many days of each month have time records.

1. Type and run the query below.

   -- Tutorial 11.1: manufacturing labor time by month
   SELECT strftime('%Y-%m', lt.WorkDate) AS WorkMonth,
       COUNT(DISTINCT lt.WorkDate) AS DaysWithTime,
       ROUND(SUM(CASE lt.LaborType WHEN 'Direct Manufacturing'
           THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 0)
           AS DirectHours,
       ROUND(SUM(CASE lt.LaborType WHEN 'Indirect Manufacturing'
           THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 0)
           AS IndirectHours,
       ROUND(SUM(lt.OvertimeHours), 0) AS OvertimeHours
   FROM LaborTimeEntry AS lt
   WHERE lt.LaborType <> 'NonManufacturing'
   GROUP BY WorkMonth
   ORDER BY WorkMonth;

2. Read the number of rows in the message pane.

3. Read the DirectHours and IndirectHours of the first six months, and the DaysWithTime of the last row.

The result has one row for each month from January 2024 to December 2026, 36 in all, so a window over it can count months by counting rows. Figure 11.2 shows the first months, which tell a story of their own. January 2024 is the month in which the data begins, when the first work orders were only starting, and most of its time is indirect. From February to May 2024 the plant recorded far less indirect time than in the months that follow, and from the summer of 2024 indirect time is a large part of every month. A month with no indirect time at all would still have a row, because its direct entries create it, and the CASE would give it an IndirectHours of zero. The last row has far fewer days with time records than any other month, because the time records end on December 11, 2026, as Tutorial 10.3 found.

Figure 11.2: Manufacturing Labor Time in the First Months of 2024.

Step 3. Find how far the time records fall short of the month’s end. The date function’s modifiers move a date to the end of its month, and julianday measures the distance between two dates.

1. Type and run the query below.

   -- Tutorial 11.1: how far the time records fall short of month-end
   SELECT MAX(WorkDate) AS LastWorkDate,
       date(MAX(WorkDate), 'start of month', '+1 month', '-1 day')
           AS MonthEnd,
       julianday(date(MAX(WorkDate), 'start of month', '+1 month',
           '-1 day')) - julianday(MAX(WorkDate)) AS DaysNotRecorded
   FROM LaborTimeEntry;

2. Compare LastWorkDate with MonthEnd, and read DaysNotRecorded.

The time records end nearly three weeks before the end of December 2026. The measure of Tutorial 11.3 therefore counts the output only up to the same day, as Tutorial 10.3 did, so that the output of its last month stops where the time records stop. That day does not show that every kind of time is complete up to it. Indirect time ends a week earlier, on December 4, 2026, as Tutorial 10.3 noted.

Step 4. Compare the overtime share of each kind of labor. Hours rise and fall with the number of working days in a month, but shares of the total can be compared from month to month. Each share divides one conditional sum by another.

1. Type and run the query below.

   -- Tutorial 11.1: the overtime share of each kind of labor by month
   SELECT strftime('%Y-%m', lt.WorkDate) AS WorkMonth,
       ROUND(SUM(CASE lt.LaborType WHEN 'Direct Manufacturing'
               THEN lt.OvertimeHours ELSE 0 END)
           / SUM(CASE lt.LaborType WHEN 'Direct Manufacturing'
               THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 3)
           AS DirectOvertimeShare,
       ROUND(SUM(CASE lt.LaborType WHEN 'Indirect Manufacturing'
               THEN lt.OvertimeHours ELSE 0 END)
           / SUM(CASE lt.LaborType WHEN 'Indirect Manufacturing'
               THEN lt.RegularHours + lt.OvertimeHours ELSE 0 END), 3)
           AS IndirectOvertimeShare
   FROM LaborTimeEntry AS lt
   WHERE lt.LaborType <> 'NonManufacturing'
   GROUP BY WorkMonth
   ORDER BY WorkMonth;

2. Compare the DirectOvertimeShare of the months of 2024 with those of 2026.

3. Compare the IndirectOvertimeShare across the three years.

The result for fiscal 2026 appears in Figure 11.3. In the first half of 2024, overtime was about a tenth of the direct hours or less. In most months of 2026 it was a quarter or more, although two months dip well below the rest, a warning that single months can mislead. Overtime on indirect labor stayed at about a twelfth of its hours throughout. March 2024 shows NULL for the indirect share, because it has no indirect hours, and SQLite returns NULL for a division by zero instead of an error. The growth in overtime is a growth in overtime on direct labor, the work that the output requires.

Figure 11.3: Overtime Shares by Kind of Labor and Month.

Step 5. Measure how long work orders take. julianday turns a date into a number of days, so the difference between two dates is a subtraction.

1. Type and run the query below.

   -- Tutorial 11.1: days from release to completion and to close
   SELECT strftime('%Y', ClosedDate) AS CloseYear,
       COUNT(*) AS WorkOrders,
       ROUND(AVG(julianday(CompletedDate) - julianday(ReleasedDate)), 1)
           AS DaysReleaseToComplete,
       ROUND(AVG(julianday(ClosedDate) - julianday(CompletedDate)), 1)
           AS DaysCompleteToClose
   FROM WorkOrder
   WHERE ClosedDate IS NOT NULL
   GROUP BY CloseYear
   ORDER BY CloseYear;

2. Compare the two averages across the three years.

The time from completion to close stayed at about the same number of days in each year. The time from release to completion more than doubled, from under three weeks for the work orders closed in fiscal 2024 to nearly six weeks for those closed in fiscal 2026. The first year’s average is held down, because a work order closed in fiscal 2024 had to be released in the same year, the first of the data. The averages group the work orders by the year they closed, so a change in the product mix or in how work moves through the plant can move them too. Check the dates themselves before reading the averages, because one work order, for example, is recorded as completed before its release. Tutorial 10.3 found that the direct hours recorded per standard hour rose only a little, so the work orders took far longer with little more recorded direct work. The dates do not show whether the orders waited, were worked more slowly, or had their time recorded against something else, which only the plant’s operating records can answer.

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 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 the controller three views of the same change: indirect time that becomes a large part of the records from the middle of 2024, after far less from February to May, more overtime on direct labor, and work orders that take longer to finish.

11.6 Window Functions

An aggregate with GROUP BY collapses rows into one row per group. A window function calculates across a set of rows but returns a value on every row, so the detail stays visible beside the calculation (SQLite, n.d.-c). You write it with OVER, which defines the window, the set of rows the function looks at for each row. SQLite added window functions in version 3.25, released in 2018. DB Browser 3.13.1 includes a much later version, and SELECT sqlite_version(); shows the version on any computer.

11.6.1 Partitions and Order

With nothing inside the parentheses, OVER (), the window is every row of the result. With PARTITION BY, it is the rows that share the current row’s values of the partition columns. The query below shows each sellable item with the average standard cost of its item group and of all the items:

SELECT ItemCode, ItemGroup, StandardCost,
    ROUND(AVG(StandardCost) OVER (PARTITION BY ItemGroup), 2)
        AS GroupAverage,
    ROUND(AVG(StandardCost) OVER (), 2) AS OverallAverage
FROM Item
WHERE ListPrice IS NOT NULL
ORDER BY ItemGroup, ItemCode;

GROUP BY ItemGroup would return one row for each group. PARTITION BY ItemGroup keeps every item and places its group’s average beside it. GROUP BY decides which rows the result has, and PARTITION BY decides which rows each calculation sees. The window never crosses from one partition into another.

ORDER BY inside OVER sets the order of the rows within each partition. For an aggregate such as SUM, it also changes the window: unless the query says otherwise, the window runs from the first row of the partition through the current row, so the sum becomes a running total. The expression below is a year-to-date total that starts again at zero in each fiscal year:

SUM(Gap) OVER (PARTITION BY FiscalYear ORDER BY FiscalPeriod)

WarningWatch out

The default window runs through the current row’s peers, the rows tied with it on the ORDER BY columns, so tied rows all show the total through the last of them (SQLite, n.d.-c). Each fiscal period appears once in each year, so the year-to-date total above has no ties. A running total of postings ordered by their date would have many; stop its window at the current row with ROWS UNBOUNDED PRECEDING, a frame that the next pages explain.

11.6.2 Ranking Rows

Ranking functions number the rows of a partition in the order of the window’s ORDER BY. ROW_NUMBER gives every row a different number. RANK gives tied rows the same number and skips the numbers after them, and DENSE_RANK gives ties the same number without skipping. The query below ranks the Furniture items by the standard labor hours each unit needs, a column in which several pairs of items tie:

SELECT ItemCode, StandardLaborHoursPerUnit,
    ROW_NUMBER() OVER (ORDER BY StandardLaborHoursPerUnit DESC)
        AS RowNum,
    RANK() OVER (ORDER BY StandardLaborHoursPerUnit DESC) AS RankNum,
    DENSE_RANK() OVER (ORDER BY StandardLaborHoursPerUnit DESC)
        AS DenseRankNum
FROM Item
WHERE ItemGroup = 'Furniture'
ORDER BY StandardLaborHoursPerUnit DESC, ItemCode
LIMIT 10;

The first two ties appear in the seventh to tenth rows. RANK and DENSE_RANK give each pair one number, and after the first pair RANK skips a number while DENSE_RANK does not. ROW_NUMBER gives the two items of a pair different numbers, in whatever order it meets them, which need not be the order of the result. When you need a stable numbering, add a column that is unique, such as ItemCode, to the window’s ORDER BY.

11.6.3 Frames

A frame clause sets exactly which rows of the partition each aggregate calculation uses. ROWS BETWEEN 11 PRECEDING AND CURRENT ROW takes the current row and the eleven rows before it, so a sum over months with that frame is a trailing twelve-month total. ROWS UNBOUNDED PRECEDING takes every row from the first of the partition to the current one, a running total that ignores ties. Figure 11.4 shows a partition and a moving frame over the months of two years. Of the functions in this chapter, a frame shapes only the aggregates over a window, such as SUM and AVG. LAG, LEAD, and the ranking functions read the whole partition in its order, whatever the frame says.

Figure 11.4: Partitions and Frames in a Window Function.

A ROWS frame counts rows, not months. Over a result with one row for every month, eleven rows back is eleven months back. If a month is missing, because nothing was recorded in it, the frame silently reaches one month further, so a query that relies on a frame should first check that it has one row for every month. Moving totals like these smooth out a month in which production or payroll happens to be lumpy, which is why management reports so often show them.

When several functions share one window, the WINDOW clause names it once. It follows the WHERE, GROUP BY, and HAVING clauses and comes before ORDER BY, and each function then refers to the window by name:

SUM(LaborHours) OVER w / SUM(StandardHours) OVER w
...
WINDOW w AS (ORDER BY Month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)

COUNT(*) OVER w counts the rows in each window, which shows where a window holds fewer than twelve rows, as the first eleven months of the data do. Twelve rows are twelve months only when no month is missing, so the count does not replace the check, above, that the result has one row for every month.

11.6.4 Comparing with Earlier Rows

Offset functions return a value from another row of the partition. LAG(x, 1) returns x from the previous row, LEAD(x, 1) from the next, and the second argument can be left out when it is 1. Over rows ordered by year and month, LAG(x, 12) returns the value of the same month of the previous year, provided that every month has a row. Partitioning by the month protects the comparison from gaps in other months: LAG(x) OVER (PARTITION BY FiscalPeriod ORDER BY FiscalYear) compares each month with the same month of the latest earlier year that has one, which is the previous year as long as no year is missing that month. The query below compares each item group’s manufacturing variance with its variance of the year before:

SELECT i.ItemGroup, strftime('%Y', woc.CloseDate) AS CloseYear,
    ROUND(SUM(woc.TotalVarianceAmount), 2) AS Variance,
    ROUND(LAG(SUM(woc.TotalVarianceAmount)) OVER (
        PARTITION BY i.ItemGroup
        ORDER BY strftime('%Y', woc.CloseDate)), 2) AS PriorYear
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID
GROUP BY i.ItemGroup, CloseYear
ORDER BY i.ItemGroup, CloseYear;

PriorYear is NULL in the first year of each group, which has no earlier row in its partition. The window repeats the expression strftime('%Y', woc.CloseDate) rather than the alias CloseYear, because an alias of the SELECT list cannot be used inside OVER; SQLite answers no such column: CloseYear. Table 11.2 summarizes the window functions of this chapter, and Table 11.3 the clauses that shape a window.

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

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

11.6.5 When Window Functions Are Calculated

In the logical order of Figure 10.5, window functions are calculated with the SELECT list, after WHERE, GROUP BY, and HAVING, and before DISTINCT, ORDER BY, and LIMIT. Two consequences follow. A window function can work on the results of a grouping, so SUM(SUM(Debit)) OVER (...) is a running total of monthly totals. But neither WHERE nor HAVING can filter on a window function’s result, because both have already run. SQLite rejects the attempt with a message that begins misuse of, such as misuse of window function RANK(), or misuse of aliased window function when the condition names the window function’s alias. To keep only the top-ranked rows, or only the months with a full twelve-month window, calculate the window function in a derived table or a common table expression, and filter the result in the outer query.

TipIn Practice

Trailing twelve-month totals and year-over-year comparisons are standard in management reporting, because they separate a trend from the swings of a single month. A plant controller who reports that labor hours per standard hour rose in March may be reporting noise. A controller who reports that the trailing twelve-month figure has risen over two years is reporting a trend in the recorded measure, provided its coverage and definitions have not changed. The price of the smoothing is delay: a trailing figure turns months after the monthly figures do, so the two are read together, and the window functions of this section produce both from the same rows.

NoteConnecting the Dots

Chapter 6 showed the same calculations in a PivotTable with Show Values As: a share of a total, a running total, and a difference from the previous item. Window functions are their SQL form, with OVER () for the share, an ordered SUM for the running total, and LAG for the difference. Show Values As can also rank the items, which RANK does in SQL. In Chapter 7 you compared each quarter with the same quarter a year earlier to judge a forecast; LAG partitioned by the month makes the same comparison for every month at once.

11.7 Guided Tutorial 11.2: Tracking the Gap Month by Month

Context and objective. In Chapter 10 you followed the conversion cost through account 1090 for whole years. In this tutorial, you will measure the gap between the actual cost charged to the account and the standard cost released by completions for each month, accumulate it through each year, compare each month with the same month a year earlier, rank the months, and find what the largest months have in common. You will then rank the work centers by the hours recorded against their planned hours.

Prerequisites. Complete Tutorial 11.1, and continue at the end of Chapter11.sql. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Chapter11.sql as it stands at the end of Tutorial 11.1 from Companion Files.

Step-by-step instructions.

Step 1. Calculate the monthly gap and its year-to-date total. The payroll and journal entries debit account 1090 with actual cost, and completions credit it with standard cost. The work order closes, which clear the difference, are left out.

1. Type and run the query below.

   -- Tutorial 11.2: the gap in account 1090 by month and year to date
   SELECT FiscalYear, FiscalPeriod,
       ROUND(SUM(Debit) - SUM(Credit), 2) AS Gap,
       ROUND(SUM(SUM(Debit) - SUM(Credit)) OVER (
           PARTITION BY FiscalYear ORDER BY FiscalPeriod), 2)
           AS GapYearToDate
   FROM GLEntry
   WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
       AND SourceDocumentType <> 'WorkOrderClose'
   GROUP BY FiscalYear, FiscalPeriod
   ORDER BY FiscalYear, FiscalPeriod;

2. Read the GapYearToDate column in the December row of each year.

The result, shown in Figure 11.5, has one row for each month. The window function sums the monthly gaps within each fiscal year, in order of period, and starts again at zero each January. Each period appears once in its year, so the default window, which includes tied rows, gives a plain running total. The December figure is the year’s total gap, and it grew in each year, to nearly twice its fiscal 2024 figure by fiscal 2026. The window works on the results of the grouping, because window functions are calculated after GROUP BY.

Figure 11.5: The Monthly Gap in Account 1090 and Its Year-to-Date Total.

Step 2. Compare each month with the same month a year earlier. LAG over a window partitioned by the fiscal period compares each month with the same month of the year before it in the data, which here is always the previous year, because every year has all twelve months.

1. Type and run the query below.

   -- Tutorial 11.2: the gap against the same month a year earlier
   SELECT FiscalYear, FiscalPeriod,
       ROUND(SUM(Debit) - SUM(Credit), 2) AS Gap,
       ROUND(LAG(SUM(Debit) - SUM(Credit)) OVER (
           PARTITION BY FiscalPeriod ORDER BY FiscalYear), 2)
           AS GapSameMonthLastYear
   FROM GLEntry
   WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
       AND SourceDocumentType <> 'WorkOrderClose'
   GROUP BY FiscalYear, FiscalPeriod
   ORDER BY FiscalYear, FiscalPeriod;

2. Compare the Gap and GapSameMonthLastYear columns for the months of fiscal 2026.

GapSameMonthLastYear is NULL for the twelve months of fiscal 2024, which have no earlier year in the data. In most months of fiscal 2026 the gap was larger than in the same month of fiscal 2025. Two comparisons stand out from the rest, though: July 2026 was nearly three times July 2025, and August 2026 was only about half of August 2025. A comparison with the same month compares like seasons, but a different number of working days or pay dates, or a change in the seasonal pattern, can still move it. Something moved the gap in those two months, and the next steps examine the pay calendar.

Step 3. Rank the months within each year. A ranking function numbers the months by the size of their gap, and a derived table lets the outer query keep the top three of each year.

1. Type and run the query below.

   -- Tutorial 11.2: the three largest months of each year
   SELECT FiscalYear, FiscalPeriod, Gap, GapRank
   FROM (
       SELECT FiscalYear, FiscalPeriod,
           ROUND(SUM(Debit) - SUM(Credit), 2) AS Gap,
           RANK() OVER (PARTITION BY FiscalYear
               ORDER BY SUM(Debit) - SUM(Credit) DESC) AS GapRank
       FROM GLEntry
       WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
           AND SourceDocumentType <> 'WorkOrderClose'
       GROUP BY FiscalYear, FiscalPeriod
   ) AS monthly
   WHERE GapRank <= 3
   ORDER BY FiscalYear, GapRank;

2. Read the three largest months of each year.

The query returns nine rows, three for each year. The ranking cannot be filtered in the inner query’s WHERE clause, because WHERE runs before the window function is calculated. The derived table calculates the rank first, and the outer WHERE keeps the top three. In each year, the largest months are far larger than a typical month.

Step 4. Count the pay dates of the largest months. Payroll is the largest debit to account 1090, and a count of the distinct posting dates of the payroll summaries shows how many payrolls each month received.

1. Copy the query of Step 3, with its comment, and paste the copy at the end of the script.

2. Edit the copy until it reads as below: change the comment, add PayDates to the outer SELECT list, and add the COUNT(DISTINCT …) expression that calculates it to the inner one. Run it.

   -- Tutorial 11.2: the three largest months with their pay dates
   SELECT FiscalYear, FiscalPeriod, Gap, PayDates, GapRank
   FROM (
       SELECT FiscalYear, FiscalPeriod,
           ROUND(SUM(Debit) - SUM(Credit), 2) AS Gap,
           COUNT(DISTINCT CASE WHEN SourceDocumentType = 'PayrollSummary'
               THEN PostingDate END) AS PayDates,
           RANK() OVER (PARTITION BY FiscalYear
               ORDER BY SUM(Debit) - SUM(Credit) DESC) AS GapRank
       FROM GLEntry
       WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
           AND SourceDocumentType <> 'WorkOrderClose'
       GROUP BY FiscalYear, FiscalPeriod
   ) AS monthly
   WHERE GapRank <= 3
   ORDER BY FiscalYear, GapRank;

3. Compare the PayDates of the months ranked first with those of the others.

The result, shown in Figure 11.6, points to the cause of the largest months. Charles River pays every two weeks, so most months have two pay dates, but a few have three, and the largest month of every year is one of them. The ledger records payroll when it is paid, and no entry at a month’s end records the wages that were earned but not yet paid, so a month with three pay dates carries three payrolls.

Figure 11.6: The Largest Months of the Gap and Their Pay Dates.

Step 5. Count the pay dates of every month. Conditional aggregation, from Tutorial 11.1, puts the pay dates of each year in a column of its own, so that every month can be compared with the same month of the other years.

1. Type and run the query below.

   -- Tutorial 11.2: pay dates by month, one column per year
   SELECT FiscalPeriod,
       COUNT(DISTINCT CASE WHEN FiscalYear = 2024
           THEN PostingDate END) AS Pay2024,
       COUNT(DISTINCT CASE WHEN FiscalYear = 2025
           THEN PostingDate END) AS Pay2025,
       COUNT(DISTINCT CASE WHEN FiscalYear = 2026
           THEN PostingDate END) AS Pay2026
   FROM GLEntry
   WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
       AND SourceDocumentType = 'PayrollSummary'
   GROUP BY FiscalPeriod
   ORDER BY FiscalPeriod;

2. Find the months with three pay dates in each year.

3. Add up the pay dates of each year.

Each year has two months with three pay dates. July 2026 had three and July 2025 two, while August 2025 had three and August 2026 two, which explains the two comparisons of Step 2 that stood out. The months with three pay dates are not months of worse performance. Single months of this account follow the pay calendar. A year-to-date or trailing total reduces the effect, because over a year the pay dates mostly even out, but it does not record the wages the ledger leaves unaccrued, and the pay dates do not quite even out between years. January 2024 shows no pay date at all, because the data begins in January and the manufacturing part of the year’s first payroll was posted to the variance account instead, so fiscal 2024 charged two payrolls fewer than the later years. Part of the growth from fiscal 2024 comes from the calendar.

TipIn Practice

Under accrual accounting, wages that employees have earned but not yet been paid at the end of a period are a liability at its end and a cost of that period: an expense, or, for the work of the plant, a cost charged to inventory through the conversion accounts. A company that closes its books every month usually records them with an accrual entry at the month’s end, reversed when the payroll is paid. The Charles River ledger has no such entry for its wages, so its monthly payroll cost follows the pay calendar. An analyst who meets a lumpy monthly series should ask how its postings are timed before reading anything into a single month.

Step 6. Rank the work centers by the hours recorded against their plan. Each work order operation records its planned hours and its work center, and the direct labor time is recorded against the operation. Only the operations that ended by the last day with time records are counted, which leaves out the operations still under way on that day, and each operation is counted in the year it ended, with all the direct time recorded against it. The cut-off does not show that all of an operation’s time has been recorded, or recorded against the right operation.

1. Type and run the query below.

   -- Tutorial 11.2: work centers ranked by hours recorded against plan
   SELECT EndYear, WorkCenterName,
       ROUND(RecordedHours - PlannedHours, 0) AS ExcessHours,
       ROUND(RecordedHours / PlannedHours, 2) AS RecordedPerPlanned,
       RANK() OVER (PARTITION BY EndYear
           ORDER BY RecordedHours / PlannedHours DESC) AS CenterRank
   FROM (
       SELECT strftime('%Y', op.ActualEndDate) AS EndYear,
           wc.WorkCenterName,
           SUM(op.PlannedLoadHours) AS PlannedHours,
           SUM(lab.Hours) AS RecordedHours
       FROM WorkOrderOperation AS op
           INNER JOIN WorkCenter AS wc ON wc.WorkCenterID = op.WorkCenterID
           LEFT JOIN (
               SELECT WorkOrderOperationID,
                   SUM(RegularHours + OvertimeHours) AS Hours
               FROM LaborTimeEntry
               WHERE LaborType = 'Direct Manufacturing'
               GROUP BY WorkOrderOperationID
           ) AS lab ON lab.WorkOrderOperationID = op.WorkOrderOperationID
       WHERE op.ActualEndDate <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
       GROUP BY EndYear, wc.WorkCenterName
   ) AS centers
   ORDER BY EndYear, CenterRank;

2. Compare the RecordedPerPlanned column across the work centers and years.

The result ranks the five work centers within each year. The derived table adds up the hours without rounding, and the outer query rounds only what it shows, so the ratios and the ranking use the exact hours. The condition on ActualEndDate also leaves out the operations that have not ended, because an ordinary comparison with NULL is never true. The pattern is lopsided. Packing and Quality Assurance record far more direct hours than their operations planned, several times more by fiscal 2026, while Assembly, Cutting, and Finishing record well under their plans. Quality Assurance plans so few hours that its ratio is high on a small base, and Packing holds nearly all the excess hours.

Step 7. Add the plant’s total. A window with PARTITION BY but no ORDER BY covers its whole partition, so it can put each year’s total beside every work center.

1. Copy the query of Step 6, with its comment, and paste the copy at the end of the script.

2. Edit the copy until it reads as below: change the comment, type a comma after AS CenterRank, and add the PlantExcessHours column below it. Run it.

   -- Tutorial 11.2: the ranking with the plant's excess hours
   SELECT EndYear, WorkCenterName,
       ROUND(RecordedHours - PlannedHours, 0) AS ExcessHours,
       ROUND(RecordedHours / PlannedHours, 2) AS RecordedPerPlanned,
       RANK() OVER (PARTITION BY EndYear
           ORDER BY RecordedHours / PlannedHours DESC) AS CenterRank,
       ROUND(SUM(RecordedHours - PlannedHours)
           OVER (PARTITION BY EndYear), 0) AS PlantExcessHours
   FROM (
       SELECT strftime('%Y', op.ActualEndDate) AS EndYear,
           wc.WorkCenterName,
           SUM(op.PlannedLoadHours) AS PlannedHours,
           SUM(lab.Hours) AS RecordedHours
       FROM WorkOrderOperation AS op
           INNER JOIN WorkCenter AS wc ON wc.WorkCenterID = op.WorkCenterID
           LEFT JOIN (
               SELECT WorkOrderOperationID,
                   SUM(RegularHours + OvertimeHours) AS Hours
               FROM LaborTimeEntry
               WHERE LaborType = 'Direct Manufacturing'
               GROUP BY WorkOrderOperationID
           ) AS lab ON lab.WorkOrderOperationID = op.WorkOrderOperationID
       WHERE op.ActualEndDate <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
       GROUP BY EndYear, wc.WorkCenterName
   ) AS centers
   ORDER BY EndYear, CenterRank;

3. Read the PlantExcessHours column of each year.

The result, shown in Figure 11.7, adds one column to the ranking of Step 6. The window OVER (PARTITION BY EndYear) adds up the excess hours of all five work centers in each year, and the total is negative in every year: the plant as a whole recorded fewer direct hours than its operations planned. A slow Packing work center would not by itself explain Assembly recording less than its plan at the same time. Hours that move from the start of the routing, the sequence of operations each work order follows, to its end while the total stays within plan are consistent with time recorded against the wrong operations, although plans that do not fit the work, the product mix, or real bottlenecks could produce the same pattern. The time records themselves need a look too. Many of the operations counted have no direct time recorded against them, and some have direct time dated after the day they ended, the late time that Chapter 12 tests. That is a finding to investigate, not yet an explanation.

Figure 11.7: Work Centers Ranked by Hours Recorded Against Plan.

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 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 window functions show that the gap grew from year to year and that its largest months come from the pay calendar, not from a few bad months. They also expose a pattern in the time records that the controller cannot explain from the numbers alone.

11.8 Common Table Expressions

A common table expression, or CTE, is a named subquery written at the start of a query with the keyword WITH. Once defined, the name can be used like a table in the rest of the query:

WITH MonthlyGap AS (
    SELECT FiscalYear, FiscalPeriod,
        SUM(Debit) - SUM(Credit) AS Gap
    FROM GLEntry
    WHERE AccountID = 92 AND FiscalYear BETWEEN 2024 AND 2026
        AND SourceDocumentType <> 'WorkOrderClose'
    GROUP BY FiscalYear, FiscalPeriod
)
SELECT FiscalYear, FiscalPeriod, ROUND(Gap, 2) AS Gap,
    ROUND(SUM(Gap) OVER (PARTITION BY FiscalYear
        ORDER BY FiscalPeriod), 2) AS GapYearToDate
FROM MonthlyGap
ORDER BY FiscalYear, FiscalPeriod;

The CTE totals, for each month, the actual cost charged to account 1090 less the standard cost released by completions, leaving out the work order closes that clear the difference to the variance account. The outer query then adds a year-to-date total. A query can define several CTEs, separated by commas, and each one can use the ones before it. A long analysis then reads from top to bottom as a series of steps, each with a name that says what it produces, instead of from the inside out as nested subqueries do. Each step can be checked on its own, by running the steps up to it with a final SELECT * FROM its name.

A CTE also gives a query one place for its parameters. A one-row CTE holds a value, and a scalar subquery from Chapter 10 reads it wherever it is needed, so that changing the value in one place changes the whole query:

WITH Params AS (
    SELECT '2026-12-31' AS AsOfDate
)
SELECT COUNT(*) AS Postings
FROM GLEntry
WHERE PostingDate <= (SELECT AsOfDate FROM Params);

The SQLite documentation describes a CTE as working like a view that exists for the length of one statement (SQLite, n.d.-d). The database is free to calculate it once and keep the result, or to treat it like a derived table, whichever it expects to be faster, so choose between a CTE and a derived table for readability, and time the query if its speed matters. For anything longer than one subquery, and for any step used more than once, a CTE is easier to follow and to review. Figure 11.8, at the start of Tutorial 11.3, shows the chain of CTEs that builds the controller’s monthly measure.

11.9 Saving an Analysis as a View

A view is a query saved in the database under a name. Once created, it can be queried like a table, and every query of it runs the saved query again against the current data. A view stores the query, not the rows, so it is never a stale copy of an earlier result (SQLite, n.d.-a). Whether the data it reads are complete, and whether the query’s assumptions still hold, is a separate question. A view cannot be changed in place, so a script that creates one drops it first, and can then be run again whenever the query changes:

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

DROP VIEW IF EXISTS removes the view, and does nothing if it does not exist yet. Leave ORDER BY out of a view: the order of the rows belongs to the query that uses the view, which adds its own ORDER BY. Views suit analyses that are run again and again, such as a monthly report, a standard audit extract, or a reconciliation. The person who uses the view does not have to understand every join inside it, and every user gets the same logic, which makes a view a control as well as a convenience. A view’s result can also leave the database: Copy with Headers and Export to CSV, from Chapter 9, turn it into a table that Excel can open and that Power BI, the tool of Part IV, can load. A monthly series built in SQL can then feed the forecasting methods of Chapter 7, or a report that is refreshed every month.

A view is the one thing in this Part that you write into the database, which is the reason for the working copy you made in Chapter 9. The view lives in CharlesRiver_Work.sqlite, and the file you downloaded is never changed. In DB Browser, a CREATE VIEW statement takes effect at once in the open database, and the view appears on the Database Structure tab, but it is saved in the file only when you click Write Changes. If you close the database first, DB Browser asks whether to save the changes, and Discard removes the view. If you need a view only for one session, CREATE TEMP VIEW creates it in a temporary database that disappears when the database is closed, and it works even in a database opened read-only.

WarningWatch out

A view depends on the tables and columns it uses. SQLite may reject a change to them at once, as it refuses to drop a column that a view uses. Or it may accept the change and leave a view that fails or changes what it means the next time someone queries it, as after a table the view reads is dropped. 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 every view with a comment in its script, stating its purpose, its source tables, and the checks its results should pass, just as you document any other query.

NoteConnecting the Dots

A chain of CTEs is the SQL form of the Applied Steps of a Power Query query in Chapters 4 and 5, where each step takes the result of the one before it and can be inspected by selecting it. A view is the counterpart of a loaded query that you refresh: the steps are kept, not their results, and each refresh, or each query of the view, runs them again on the current data.

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

Context and objective. The controller wants one measure she can track every month: labor hours per standard hour of output, split between direct and indirect time, with the share of overtime, smoothed over a trailing twelve months. In this tutorial, you will build it as a chain of common table expressions, running and checking each step before you add the next. Figure 11.8 shows the chain you will build. You will then save it as a view in your working copy of the database, so that it can be run again with one short query.

Prerequisites. Complete Tutorials 11.1 and 11.2, and continue at the end of Chapter11.sql. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Chapter11.sql as it stands at the end of Tutorial 11.2 from Companion Files. The view is saved in CharlesRiver_Work.sqlite, never in the file you downloaded. Figure 10.8 shows the labor time table that the hours step summarizes.

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

Step-by-step instructions.

Step 1. Build the output step and check it. The first CTE turns the production completions into standard hours by month. Its WHERE clause counts only the output completed up to the last day with time records, as Tutorial 10.3 did, so that the output does not run on after the hours stop. That day is the last of any time record, not of every kind of time, as Tutorial 11.1 found.

1. Type and run the query below.

   -- Tutorial 11.3: step 1, the standard hours of output by month
   WITH MonthlyOutput AS (
       SELECT strftime('%Y-%m', pc.CompletionDate) AS Month,
           SUM(pcl.QuantityCompleted * i.StandardLaborHoursPerUnit)
               AS StandardHours
       FROM ProductionCompletionLine AS pcl
           INNER JOIN ProductionCompletion AS pc
               ON pc.ProductionCompletionID = pcl.ProductionCompletionID
           INNER JOIN Item AS i ON i.ItemID = pcl.ItemID
       WHERE pc.CompletionDate
           <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
       GROUP BY Month
   )
   SELECT * FROM MonthlyOutput
   ORDER BY Month;

2. Read the number of rows, and the StandardHours of the first and last months.

The step returns one row for each of the 36 months, with the standard hours of the units completed. January 2024 has very little output, because it is the month in which the data begins, and December 2026 has less than a usual month, because its output stops on December 11.

Step 2. Build the hours step and check it. The second CTE summarizes the manufacturing labor time by month with conditional aggregation, as Tutorial 11.1 did, but without rounding, because its sums feed later steps.

1. Type and run the query below.

   -- Tutorial 11.3: step 2, the labor hours by month
   WITH MonthlyHours AS (
       SELECT strftime('%Y-%m', WorkDate) AS Month,
           SUM(CASE LaborType WHEN 'Direct Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS DirectHours,
           SUM(CASE LaborType WHEN 'Indirect Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS IndirectHours,
           SUM(OvertimeHours) AS OvertimeHours
       FROM LaborTimeEntry
       WHERE LaborType <> 'NonManufacturing'
       GROUP BY Month
   )
   SELECT * FROM MonthlyHours
   ORDER BY Month;

2. Read the number of rows, and the IndirectHours of March 2024.

This step also returns 36 rows. March 2024 has a row with zero indirect hours, because its direct entries create the row and the CASE passes zero, so no month is missing for a window to skip. Had the indirect hours been summarized in a step of their own, with WHERE LaborType = 'Indirect Manufacturing', March 2024 would have had no row at all.

Step 3. Join the two steps, and check the join. The third CTE joins output and hours on the month. A check query counts its rows, because an inner join drops any month that is missing from either step.

1. Copy the query of Step 1, with its comment, to the end of the script, and change the comment to the one below.

2. Delete the copy’s last two lines, type a comma after the closing parenthesis of MonthlyOutput, and paste the MonthlyHours step of Step 2 after it, from MonthlyHours AS ( to its closing parenthesis.

3. Type a comma, the Monthly step, and the check query, so that the query reads as below. Run it.

   -- Tutorial 11.3: step 3, the two steps joined by month
   WITH MonthlyOutput AS (
       SELECT strftime('%Y-%m', pc.CompletionDate) AS Month,
           SUM(pcl.QuantityCompleted * i.StandardLaborHoursPerUnit)
               AS StandardHours
       FROM ProductionCompletionLine AS pcl
           INNER JOIN ProductionCompletion AS pc
               ON pc.ProductionCompletionID = pcl.ProductionCompletionID
           INNER JOIN Item AS i ON i.ItemID = pcl.ItemID
       WHERE pc.CompletionDate
           <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
       GROUP BY Month
   ),
   MonthlyHours AS (
       SELECT strftime('%Y-%m', WorkDate) AS Month,
           SUM(CASE LaborType WHEN 'Direct Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS DirectHours,
           SUM(CASE LaborType WHEN 'Indirect Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS IndirectHours,
           SUM(OvertimeHours) AS OvertimeHours
       FROM LaborTimeEntry
       WHERE LaborType <> 'NonManufacturing'
       GROUP BY Month
   ),
   Monthly AS (
       SELECT o.Month, o.StandardHours, h.DirectHours,
           h.IndirectHours, h.OvertimeHours
       FROM MonthlyOutput AS o
           INNER JOIN MonthlyHours AS h ON h.Month = o.Month
   )
   SELECT COUNT(*) AS Months FROM Monthly;

The query defines three steps and counts the rows of the third. It returns 36, the same as each step on its own, so every month found its match. With every month from January 2024 to December 2026 present, no month is missing between the first and the last, which the trailing window needs. Check that again on a later run, because twelve rows in a window are twelve months only when no month is missing. A CTE can be run only together with the steps it uses, so each check repeats the steps before it, and the query is complete at every stage.

Step 4. Add the trailing twelve months. The fourth step calculates the monthly ratio and the trailing twelve-month figures over a named window, and the final SELECT keeps the months whose window holds twelve monthly rows. In the column names, TTM stands for trailing twelve months, the current month and the eleven before it. StandardHours is the labor time that the standards allow for the month’s completed output. DirectHours and IndirectHours each include regular and overtime hours, so overtime is already part of them. Each trailing ratio divides the pooled hours of the twelve months by their pooled StandardHours, and TTMOvertimeShare divides the pooled overtime hours by the pooled direct and indirect hours.

1. Copy the query of Step 3, with its comment, to the end of the script, and change the comment to the one below.

2. Delete the last line, SELECT COUNT(*) AS Months FROM Monthly;, type a comma after the closing parenthesis of the Monthly step, and add the Trailing step and the final SELECT, so that the query reads as below. Run it.

   -- Tutorial 11.3: step 4, the trailing twelve months
   WITH MonthlyOutput AS (
       SELECT strftime('%Y-%m', pc.CompletionDate) AS Month,
           SUM(pcl.QuantityCompleted * i.StandardLaborHoursPerUnit)
               AS StandardHours
       FROM ProductionCompletionLine AS pcl
           INNER JOIN ProductionCompletion AS pc
               ON pc.ProductionCompletionID = pcl.ProductionCompletionID
           INNER JOIN Item AS i ON i.ItemID = pcl.ItemID
       WHERE pc.CompletionDate
           <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
       GROUP BY Month
   ),
   MonthlyHours AS (
       SELECT strftime('%Y-%m', WorkDate) AS Month,
           SUM(CASE LaborType WHEN 'Direct Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS DirectHours,
           SUM(CASE LaborType WHEN 'Indirect Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS IndirectHours,
           SUM(OvertimeHours) AS OvertimeHours
       FROM LaborTimeEntry
       WHERE LaborType <> 'NonManufacturing'
       GROUP BY Month
   ),
   Monthly AS (
       SELECT o.Month, o.StandardHours, h.DirectHours,
           h.IndirectHours, h.OvertimeHours
       FROM MonthlyOutput AS o
           INNER JOIN MonthlyHours AS h ON h.Month = o.Month
   ),
   Trailing AS (
       SELECT Month,
           ROUND((DirectHours + IndirectHours) / StandardHours, 2)
               AS HoursPerStandardHour,
           ROUND(SUM(DirectHours) OVER w
               / SUM(StandardHours) OVER w, 2) AS TTMDirect,
           ROUND(SUM(IndirectHours) OVER w
               / SUM(StandardHours) OVER w, 2) AS TTMIndirect,
           ROUND(SUM(DirectHours + IndirectHours) OVER w
               / SUM(StandardHours) OVER w, 2) AS TTMTotal,
           ROUND(SUM(OvertimeHours) OVER w
               / SUM(DirectHours + IndirectHours) OVER w, 3)
               AS TTMOvertimeShare,
           COUNT(*) OVER w AS MonthsInWindow
       FROM Monthly
       WINDOW w AS (ORDER BY Month
           ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
   )
   SELECT Month, HoursPerStandardHour, TTMDirect, TTMIndirect,
       TTMTotal, TTMOvertimeShare
   FROM Trailing
   WHERE MonthsInWindow = 12
   ORDER BY Month;

3. Read the TTMDirect, TTMIndirect, and TTMTotal columns from the first row to the last.

4. Read the TTMOvertimeShare column.

The WINDOW clause names the window, its order and its frame, once as w, and all nine window functions of the step use it. TTMDirect, TTMIndirect, and TTMTotal are the direct, indirect, and total hours per standard hour over the current month and the eleven before it, and MonthsInWindow counts the rows, one for each month, in each window. Each trailing figure divides the hours of its twelve months by their standard hours, so a month with more output counts for more: it is not the average of twelve monthly ratios. TTMTotal is calculated from the unrounded hours, so it equals the sum of the two rounded ratios beside it only to within rounding. Read each ratio as hours per standard hour. A TTMTotal near one and a half, for example, means that the plant recorded about an hour and a half, direct and indirect together, for every hour the standards allow for its output. It is a ratio, not a percentage increase and not a formal labor efficiency variance, because indirect time has no allowed hours of its own in the denominator. The result starts in December 2024, the first month whose window holds twelve months. The monthly ratio jumps around, but the trailing figures tell a clear story. TTMIndirect climbs through the first half of fiscal 2025, as the months of 2024 with little indirect time leave the window, and then holds at a little over half an hour per standard hour. TTMDirect stays below one, but it creeps up through fiscal 2026. TTMTotal reaches its highest point in October 2026, and TTMOvertimeShare climbs in most months, from about a ninth of the hours to about a fifth. Each December’s figures equal the ratios of Tutorial 10.3 for the year, because both count the labor time by the day worked and the output up to the last day with time records, and like those ratios they are an observed-records diagnostic. The last row, December 2026, has a full window of twelve monthly rows, but its direct time and output run to December 11, 2026, and its indirect time only to December 4, 2026, so read it as provisional. Counted to that earlier day on both sides, hours and output alike, its direct ratio is somewhat lower, its indirect ratio a little higher, and its total a little lower.

Step 5. Look at the months before the window is full. The final SELECT keeps only the months whose window holds twelve months, and reversing the condition shows why.

1. In the query of Step 4, change WHERE MonthsInWindow = 12 to WHERE MonthsInWindow < 12, and run the query.
2. Read the TTMTotal column of the first rows.
3. Change the condition back to WHERE MonthsInWindow = 12.

The query returns the months of 2024 before December, whose windows hold from one to eleven months. January 2024’s window holds only the start-up month, when the plant recorded hours but completed almost nothing, and its figure is many times any other. The figures settle as the window fills, but they combine fewer months than the rest, so keeping them would compare unlike periods. The first full window, December 2024, still includes January 2024, which makes it a little high; the start-up month leaves the window when it moves to January 2025.

Step 6. Save the measure as a view. A view stores the query under a name, and the name can then be queried like a table. The statements drop any earlier version of the view first, so that they can be run again whenever the query changes.

1. Copy the query of Step 4 to the end of the script, and change its comment to -- Tutorial 11.3: the measure saved as a view.

2. Delete the last line of the copy, ORDER BY Month;, and add a semicolon at the end of the line WHERE MonthsInWindow = 12.

3. Add the lines DROP VIEW IF EXISTS MonthlyLaborEfficiency; and CREATE VIEW MonthlyLaborEfficiency AS above WITH, so that the statements read as below.

   -- Tutorial 11.3: the measure saved as a view
   DROP VIEW IF EXISTS MonthlyLaborEfficiency;
   CREATE VIEW MonthlyLaborEfficiency AS
   WITH MonthlyOutput AS (
       SELECT strftime('%Y-%m', pc.CompletionDate) AS Month,
           SUM(pcl.QuantityCompleted * i.StandardLaborHoursPerUnit)
               AS StandardHours
       FROM ProductionCompletionLine AS pcl
           INNER JOIN ProductionCompletion AS pc
               ON pc.ProductionCompletionID = pcl.ProductionCompletionID
           INNER JOIN Item AS i ON i.ItemID = pcl.ItemID
       WHERE pc.CompletionDate
           <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
       GROUP BY Month
   ),
   MonthlyHours AS (
       SELECT strftime('%Y-%m', WorkDate) AS Month,
           SUM(CASE LaborType WHEN 'Direct Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS DirectHours,
           SUM(CASE LaborType WHEN 'Indirect Manufacturing'
               THEN RegularHours + OvertimeHours ELSE 0 END)
               AS IndirectHours,
           SUM(OvertimeHours) AS OvertimeHours
       FROM LaborTimeEntry
       WHERE LaborType <> 'NonManufacturing'
       GROUP BY Month
   ),
   Monthly AS (
       SELECT o.Month, o.StandardHours, h.DirectHours,
           h.IndirectHours, h.OvertimeHours
       FROM MonthlyOutput AS o
           INNER JOIN MonthlyHours AS h ON h.Month = o.Month
   ),
   Trailing AS (
       SELECT Month,
           ROUND((DirectHours + IndirectHours) / StandardHours, 2)
               AS HoursPerStandardHour,
           ROUND(SUM(DirectHours) OVER w
               / SUM(StandardHours) OVER w, 2) AS TTMDirect,
           ROUND(SUM(IndirectHours) OVER w
               / SUM(StandardHours) OVER w, 2) AS TTMIndirect,
           ROUND(SUM(DirectHours + IndirectHours) OVER w
               / SUM(StandardHours) OVER w, 2) AS TTMTotal,
           ROUND(SUM(OvertimeHours) OVER w
               / SUM(DirectHours + IndirectHours) OVER w, 3)
               AS TTMOvertimeShare,
           COUNT(*) OVER w AS MonthsInWindow
       FROM Monthly
       WINDOW w AS (ORDER BY Month
           ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
   )
   SELECT Month, HoursPerStandardHour, TTMDirect, TTMIndirect,
       TTMTotal, TTMOvertimeShare
   FROM Trailing
   WHERE MonthsInWindow = 12;

4. Select both statements and run them.

The message pane reports that execution finished without errors and that the query executed successfully, and no result grid appears, because the statements return no rows.

Step 7. Find the view, and write it to the file. The view exists in the open database at once, but it is saved in the file only when you write the changes.

1. On the Database Structure tab, find MonthlyLaborEfficiency under Views.
2. Click Write Changes on the toolbar, which the new view has made available, to save the view in CharlesRiver_Work.sqlite.

The view is now part of your working copy, as Figure 11.9 shows. It holds the query, not its result, so it will reflect any change in the data. Its six columns are the columns of the final SELECT, and it has no ORDER BY, because the order of the rows belongs to the query that uses it.

Figure 11.9: The Saved View in the Database Structure Tab.

Step 8. Query the view. The whole chain of steps now runs from one short query.

1. On the Execute SQL tab, type and run the query below at the end of the script.

   -- Tutorial 11.3: the view for fiscal 2026
   SELECT *
   FROM MonthlyLaborEfficiency
   WHERE Month BETWEEN '2026-01' AND '2026-12'
   ORDER BY Month;

2. Compare the result with the fiscal 2026 rows of Step 4.

The result, shown in Figure 11.10, holds the twelve months of fiscal 2026, with the same values as Step 4. The controller, or anyone else with the working copy, can run the view each month, after the new month’s data arrives, with the months of the WHERE clause moved forward, and see at once whether the measure is still rising. The view does not check its own coverage, so make a short check part of every run. Before each rerun, record which version of the data you ran it on and where each kind of manufacturing labor ends, and confirm that the output step and the hours step each have one row for every month, with the same months in both, because the inner join drops a month that is missing from either. Look for a month with no standard hours, because SQLite answers a division by zero with NULL, not with an error. The output stops at the last day with any time record, nonmanufacturing time included, so confirm that this is the cutoff you intend, and keep a short note of the coverage with the result you export. Twelve rows in a window are twelve months only after that calendar check, and a matching cutoff still does not show that every hour was recorded, so treat the latest month as provisional until its records are complete.

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

Step 9. Complete and save the script. The header comment should record the checks that the chapter’s queries passed.

1. Replace the last line of the header comment of Chapter11.sql, so that the header reads as below.

   /* Chapter 11: the plant's hours, month by month
      Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
      Prepared by: your name, date
      Checks: the payroll ties to the ledger; 36 months in, 25 in the view */

2. Click Save SQL file.

The script now holds every query of the chapter’s tutorials under a comment that says what it answers, and a header that records the checks the results passed.

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 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 that is 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 point the same way. The records show much more indirect time since the middle of 2024 for about the same output, and a rising share of the direct hours paid at overtime rates. Before the controller reads that as time spent away from the work orders, changes in activity, staffing, the classification of time, and the completeness of the records need to be ruled out.

11.11 Looking Ahead

This chapter has added four tools to your SQL. You classified rows with CASE and turned categories into columns with conditional aggregation. You used strftime, date, and julianday to group transactions by month, to find how far the time records fall short of a month’s end, and to measure how long work orders took. You applied window functions to calculate year-to-date totals, compare each month with the same month a year earlier, rank months and work centers, and build trailing twelve-month measures. Finally, you organized the controller’s monthly measure as a chain of common table expressions and saved it as a view, which she can now run again whenever the data changes. In the terms of Table 1.1, this was diagnostic analytics, tracing when and where the extra hours arose. The trailing measure also prepares the ground for predictive work: a trailing twelve-month figure is a baseline against which each new month can be judged, and the forecasting methods of Chapter 7 could project it.

The measure answers her four questions as far as the records go. The change they record began in the middle of fiscal 2024. Indirect time is far below its later level from February to May 2024 and becomes a large part of every month from the summer of 2024, and the overtime share of direct labor started to climb in the same summer. Whether it is still getting worse depends on the kind of labor. Indirect time per standard hour has been roughly flat since the middle of fiscal 2025, while direct hours per standard hour crept up through fiscal 2026, still within the standard, and the trailing measure reached its highest point in October 2026. By the end of fiscal 2026 about a fifth of all the hours were overtime, and the share had risen only on direct labor, where more than a quarter of the hours were paid at overtime rates. And the time recorded against work orders is concentrated in a way that the plant’s totals hide: the Packing and Quality Assurance work centers recorded several times the hours their operations planned, while Assembly, Cutting, and Finishing recorded well under their plans.

The controller’s explanation will go to the audit committee, and its first question will be whether the records behind it are reliable. Were the hours worked, recorded against the right operations, approved, and paid correctly? Why does indirect time almost vanish from the records for four months? That is a question about the reliability of the records rather than their meaning, and in Chapter 12 the internal auditors take it up.

11.12 Chapter Summary

Many analytical questions need more than grouping. A CASE expression classifies each row by conditions tested in order, and its ELSE decides what happens to rows that meet none of them; the simple form compares one expression with a list of values, and never matches NULL. The Charles River dates are stored as text, and SQLite’s date functions work on that text. strftime formats a date into a period such as a year and month, and date moves a date with modifiers, applied in order, such as start of month; 'start of month', '+1 month', '-1 day' gives a month’s last day. julianday turns a date into a number of days, so that the difference between two dates is a subtraction. strftime and date return text, and julianday a number, so their results must be compared with values of the same kind or converted with CAST. Placed inside an aggregate, CASE turns categories into columns, which is conditional aggregation, the SQL counterpart of SUMIFS and of a PivotTable.

A window function calculates across a set of rows but keeps every row. OVER () makes the window the whole result, PARTITION BY divides the rows into groups without collapsing them, and ORDER BY sets their order and, for an aggregate, makes a running total whose default window includes tied rows. A frame such as ROWS BETWEEN 11 PRECEDING AND CURRENT ROW decides which rows each calculation uses, and it counts rows, not months, so it needs one row for every month. The WINDOW clause names a window that several functions share. Ranking functions, such as ROW_NUMBER, RANK, and DENSE_RANK, number the rows and treat ties differently. LAG and LEAD return values from earlier and later rows, and LAG partitioned by the month compares each month with the same month of the latest earlier year in the data. Window functions are calculated with the SELECT list, after grouping, so they can work on grouped results, but neither WHERE nor HAVING can filter on them without a derived table or a common table expression.

A common table expression names a subquery at the start of a query with WITH, so that a long analysis reads as a series of steps that can each be checked on their own, and a one-row CTE can hold a query’s parameters. A view saves a query in the database under a name. It stores the query rather than its result, so it always reflects the current data; a script drops it and creates it again to change it, and it is written only into the working copy, never into the downloaded file.

Applied to the controller’s questions, these tools produced a monthly measure of direct, indirect, and total labor hours per standard hour, with the share of overtime, smoothed over a trailing twelve months, cut off at the last day with time records, and saved as a view. The payroll classified with CASE tied to the ledger’s charge to the cent. Apart from the start-up month, indirect time is almost absent from the records from February to May 2024 and present every month from June 2024, and per standard hour it has been roughly flat since the middle of 2025, while direct hours per standard hour and the overtime share of direct labor kept rising through fiscal 2026. The largest months of the gap in account 1090 turned out to be months with three pay dates, because payroll is recorded when it is paid. Work orders closed in fiscal 2026 took more than twice as long from release to completion as those closed in fiscal 2024. The ranking of work centers also exposed a pattern in the time records: far more hours than planned at the end of the routing, and far fewer at its start, while the plant as a whole stayed within plan. That pattern is for the auditors of Chapter 12 to test.

11.13 Key Terms

CASE expression. An expression that returns a value chosen by conditions, tested in the order written, with the value of the first true condition returned. A CASE without ELSE returns NULL for rows that meet no condition. The simple form, CASE x WHEN value THEN ..., compares one expression with a list of values using =, so it never matches NULL.

Common table expression (CTE). A named subquery defined at the start of a query with WITH, which the rest of the query can use like a table. Several CTEs in a row turn a long analysis into steps that can be read and checked one at a time.

Conditional aggregation. Calculating a total, count, average, minimum, or maximum over only the rows that meet one or more criteria, as SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, and MAXIFS do. In SQL, a CASE expression inside an aggregate function, such as SUM(CASE WHEN ... THEN x ELSE 0 END), does the same, so that each category of a column becomes a column of its own in the result, as in a PivotTable.

Date function. A function that reads, formats, or moves a date. In SQLite, strftime formats a date into parts such as a year or a month, date moves a date with modifiers applied in order, and julianday converts a date to a number of days.

Frame. The part of a window partition that an aggregate window function’s calculation uses for each row, such as ROWS BETWEEN 11 PRECEDING AND CURRENT ROW, the current row and the eleven before it. A ROWS frame counts rows, not periods. Without a frame clause, an ordered window runs from the first row of the partition through the current row and the rows tied with it. LAG, LEAD, and the ranking functions ignore the frame.

LAG and LEAD. Window functions that return a value from an earlier or a later row of the partition. Over rows ordered by month, with one row for every month, LAG(x, 12) returns x for the same month of the previous year; partitioned by the month and ordered by the year, LAG(x) returns the same month of the latest earlier year that has one, which is the previous year when no year is missing.

Named window. A window defined once in a WINDOW clause, as in WINDOW w AS (ORDER BY Month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW), and used by several window functions with OVER w.

Partition. A group of rows defined by PARTITION BY in a window function. The window never crosses from one partition into another, so a running total partitioned by fiscal year restarts each year. Unlike GROUP BY, PARTITION BY keeps every row.

Ranking function. A window function that numbers the rows of a partition in order. ROW_NUMBER gives every row a different number, RANK gives ties the same number and skips the numbers after them, and DENSE_RANK gives ties the same number without skipping.

Running total. A total that accumulates row by row in a set order, such as a year-to-date total. In SQL it is an aggregate window function with ORDER BY, often partitioned by period.

Trailing twelve months. A measure calculated over the current month and the eleven before it, which smooths out the swings of single months. In SQL it is a window with the frame ROWS BETWEEN 11 PRECEDING AND CURRENT ROW, over a result with one row for every month.

View. A query saved in the database under a name, which can be queried like a table. A view stores the query, not the rows, so every query of it reflects the current data. It is changed by dropping it and creating it again.

Window function. A function that calculates across a set of rows, its window, while returning a value on every row, written with OVER. Window functions are calculated with the SELECT list, after WHERE, GROUP BY, and HAVING, so neither WHERE nor HAVING can filter on them.

11.14 Multiple Choice Questions

1. A CASE expression classifies payroll lines into three categories and has no ELSE. What does it return for a line that meets none of the conditions?

1. The value of the last category listed
2. An error, so the query stops
3. The text of the LineType column unchanged
4. NULL

2. A CASE expression tests WHEN Amount > 1000 THEN 'Large' before WHEN Amount > 10000 THEN 'Very large'. What happens to an amount of 25,000?

1. It is classified as Very large, because that condition is more specific
2. It is classified as Large, because the first true condition wins
3. It is classified as Very large, because the larger threshold is tested first
4. It returns NULL, because CASE cannot choose between two true conditions

3. Which expression totals only the overtime earnings in a query grouped by month?

1. SUM(Amount) WHERE LineType = 'Overtime Earnings'
2. COUNT(CASE WHEN LineType = 'Overtime Earnings' THEN Amount END)
3. SUM(CASE WHEN LineType = 'Overtime Earnings' THEN Amount ELSE 0 END)
4. CASE WHEN LineType = 'Overtime Earnings' THEN SUM(Amount) END

4. What does strftime('%Y-%m', WorkDate) return for a WorkDate of 2026-03-06?

1. The text 2026-03
2. The number 202603
3. The date 2026-03-01
4. The text March 2026

5. How do you calculate the number of days between a work order’s ReleasedDate and its CompletedDate in SQLite?

1. CompletedDate - ReleasedDate
2. DATEDIFF(day, ReleasedDate, CompletedDate)
3. strftime('%d', CompletedDate) - strftime('%d', ReleasedDate)
4. julianday(CompletedDate) - julianday(ReleasedDate)

6. The condition strftime('%Y', PostingDate) = 2026 returns no rows, although the ledger holds hundreds of thousands of postings in 2026. Why?

1. strftime cannot read dates that include a time
2. PostingDate is stored as a number, which strftime cannot read
3. strftime returns text, which does not equal the number 2026
4. The condition must be written in a HAVING clause

7. With exactly one row for each fiscal year and fiscal period, what does SUM(Gap) OVER (PARTITION BY FiscalYear ORDER BY FiscalPeriod) return on each row?

1. The total of the year’s gaps up to and including that month
2. The total gap of the whole fiscal year, repeated on every monthly row
3. The gap of the previous month in the same fiscal year
4. The total of all the gaps in the table, across every fiscal year

8. Over rows ordered by fiscal year and period, with one row for every month, what does LAG(Gap, 12) return on a row that has at least twelve earlier rows?

1. The average gap of the last twelve months, including this one
2. The gap of the same month of the previous year
3. The gap of the month twelve months later, in the next year
4. The twelfth largest gap in the whole table

9. Two work centers have the same ratio of recorded to planned hours. What is the difference between RANK and ROW_NUMBER for them?

1. RANK gives them different numbers, and ROW_NUMBER gives them the same number
2. RANK leaves them out of the result, and ROW_NUMBER keeps them
3. They give identical results whenever values are tied
4. RANK gives them the same number, and ROW_NUMBER gives them different numbers

10. An analyst wants only the months ranked first through third within each year, keeping any ties, from a query that ranks months with RANK() OVER (…). Why can the condition not go in that query’s WHERE clause?

1. WHERE cannot compare the number a function returns with 3
2. WHERE runs before window functions, so no rank exists yet
3. RANK can only be used in an ORDER BY clause, not in a condition
4. A WHERE clause cannot appear in a query that also uses GROUP BY

11. A query calculates SUM(Amount) OVER (ORDER BY PostingDate) over ledger lines, and two lines share a PostingDate. What does the running total show on those two lines?

1. The same total on both, including the amounts of both lines
2. A total on the first that leaves out the second, and on the second that includes both
3. NULL on both, because the order of tied rows is undefined
4. An error, because the ORDER BY inside OVER must be unique

12. What is the main advantage of writing a long analysis as a chain of common table expressions?

1. CTEs always run faster than the same query written with derived tables
2. CTEs save their results in the database for use by later queries
3. Each step is named and can be checked on its own, from the top down
4. A CTE is the only way to use the same subquery twice in one query

13. What does a view such as MonthlyLaborEfficiency store in the database?

1. A copy of the rows its query returned when the view was created
2. A summary table that must be refreshed by hand after each change
3. The result of its query as of the last time Write Changes was clicked
4. The query itself, which runs on the current data each time

14. What is the difference between GROUP BY ItemGroup in a query and PARTITION BY ItemGroup in a window function?

1. GROUP BY sorts the rows by group, while PARTITION BY only filters them
2. GROUP BY returns one row per group, while PARTITION BY keeps every row
3. PARTITION BY returns one row per group, while GROUP BY keeps every row
4. They are two spellings of one clause, and they give the same result

15. The monthly ratio of labor hours to standard hours swings widely, while the trailing twelve-month ratio rises over two years. What should the controller conclude?

1. The recorded measure suggests a sustained rise, if its coverage and definitions held
2. The monthly figures are wrong, because they differ from the trailing ones
3. Nothing, because a trailing twelve-month measure always rises over time
4. The swings show that the plant’s efficiency improved in some months

11.15 Applied Exercises

Work on these exercises in CharlesRiver_Work.sqlite, and save the queries of each exercise in a script of its own, named after the exercise (such as Ex 11.1.sql). Organize any query of more than one step as common table expressions, and check each step on its own before you rely on the result. If an exercise asks you to save a view, give it a name that begins with the exercise number, such as Ex11_1_IncomeStatement, and never change or drop the tutorial’s view.

11.15.1 Financial Accounting Exercises

Exercise 11.1: The Financial Statements from the Ledger

Table group: Accounting Core

Scenario. In Exercise 6.1 you turned the pre-closing trial balance of fiscal 2026 into a common-size income statement in Excel, and in Tutorial 10.2 you rebuilt the trial balance with SQL. The controller asks for both statements from a single query, so that they can be produced for any year-end by changing one date.

Requirements.

1. Write a CTE that holds the as-of date, a second that finds the year’s closing entries to leave out (the journal entries whose EntryType begins with Year-End Close and whose PostingDate is the as-of date), and a third that builds the pre-closing trial balance at that date, with each account’s type and subtype and its balance expressed as credits less debits, so that revenue, liabilities, and equity are positive and assets and expenses negative. Read the date with a scalar subquery wherever it is needed.

2. Use a CASE expression on AccountType and AccountSubType to assign each account to a statement line: operating revenue, contra revenue, cost of goods sold, operating expenses, other income and expense, current assets, fixed assets, contra fixed assets, current liabilities, long-term liabilities, and equity.

3. Total the lines, and calculate net revenue, gross margin, operating income, and net income. Show gross margin and operating income as percentages of net revenue. The totals can be columns of one row, built by conditional aggregation; to stack them below the lines instead, join SELECT statements that return the same columns in the same order with UNION ALL, which keeps every row of each and which Chapter 12 explains.

4. Show that total assets equal total liabilities plus equity plus net income, and compare net income with the closing entry of fiscal 2026 that moves it to retained earnings.

5. Save the query as a view, without an ORDER BY, and query the view with a CASE expression in ORDER BY that sorts the statement lines in statement order.

Deliverable. A script and a view that produce the income statement and the balance sheet check, with a short note comparing the results with Chapter 6.

Exercise 11.2: Monthly Revenue with Year-to-Date and Prior-Year Comparisons

Table group: Accounting Core

Scenario. Chapter 1 promised that SQL would support the period-over-period comparisons behind a forecast. The controller asks for a monthly revenue report by product line for fiscal 2024 through 2026, taken from the ledger, with a year-to-date total and a comparison with the same month of the prior year.

Requirements.

1. Total the credits less debits posted to the revenue accounts 4010 to 4040 and 4080 by fiscal year, fiscal period, and account, leaving out the year-end closing entries. Use CASE to name each account’s product line.

2. Add a year-to-date running total for each product line, partitioned by product line and fiscal year.

3. Add the revenue of the same month of the previous year with LAG, partitioned by product line and fiscal period and ordered by fiscal year, and the percentage change.

4. Confirm that the December year-to-date figures of fiscal 2026 equal the balances of accounts 4010 to 4040 and 4080 in the trial balance of Tutorial 10.2.

5. For Furniture, identify the months of fiscal 2026 with the largest decline and the largest increase against the prior year, and suggest what you would look at to explain them.

Deliverable. A script with the monthly report and a short note on the Furniture comparison.

11.15.2 Managerial Accounting Exercises

Exercise 11.3: Ranking Items by Unit Margin Within Each Product Line

Table group: Master Data

Scenario. The product manager wants to know which items earn the most per unit at list price within each product line, and how wide the range is. The controller asks you for a ranking that can be refreshed when prices or standard costs change.

Requirements.

1. For every item with a list price, calculate the unit margin at list price, ListPrice minus StandardCost, and the margin as a percentage of ListPrice, both rounded.

2. Rank the items within each item group by unit margin with RANK, and add DENSE_RANK and ROW_NUMBER beside it. Explain when the three would differ, and whether they differ here.

3. Keep the top three items of each item group, using a CTE and an outer filter.

4. For every item with a list price, not only the three kept in (3), show the highest and lowest unit margins of its item group and the number of items in the group, using window functions rather than GROUP BY, so that each item row keeps its detail.

5. Explain why the Services items show a margin equal to their price, and why this measure is a gross margin at list price rather than a contribution margin.

Deliverable. A script with the ranking and a short note for the product manager.

Exercise 11.4: Is Overtime Concentrated?

Table group: Manufacturing, Payroll and Time

Scenario. The plant manager’s first response to the controller’s finding is that overtime is a problem of a few people, not of the plant. The controller asks you to test that claim with the fiscal 2026 time records.

Requirements.

1. Total the fiscal 2026 overtime hours of each employee from the manufacturing labor time, by work date, with the employee’s job title.

2. Rank the employees by overtime hours, and add a running total of overtime as a share of the whole, ordered from the highest to the lowest.

3. Find how many employees account for half of the overtime.

4. Calculate the overtime hours and the average per employee by job title.

5. Write a short memo (one half page) to the controller answering the plant manager’s claim.

Deliverable. A script with the queries and a half-page memo.

11.15.3 Auditing Exercises

Exercise 11.5: Journal Entry Tests in SQL

Table group: Accounting Core, Master Data

Scenario. In Tutorial 8.3 you flagged journal entries with five tests in Excel and combined the flags into a risk score. The chief audit executive wants the tests in a saved query that can be rerun on next year’s entries, and asks you to show that the SQL version gives the same results as the workbook.

Requirements.

1. Join JournalEntry to Employee on the approver, and write a CTE with five flags. Each flag is a CASE expression that returns 1 or 0: Weekend (created on a Saturday or Sunday, using strftime on CreatedDate), Backdated (created on a later day than its posting date), SelfApproved, AboveLimit (TotalAmount above the approver’s MaxApprovalAmount), and RoundAmount (TotalAmount in exact thousands).

2. Add a RiskScore that sums the five flags, and count the entries flagged by each test and the entries at each score.

3. Compare the counts with your results from Tutorial 8.3.

4. List the entries with a score of 2 or more, ranked by score and then by TotalAmount, with RANK.

5. Save the query as a view, and explain in two or three sentences how a saved, reviewed query helps an auditor apply a test consistently and perform it again, and which controls it still needs.

Deliverable. A script and a view with the journal entry tests, and a comparison with Chapter 8.

Exercise 11.6: Requisitions Just Under the Approval Threshold

Table group: Procure-to-Pay, Master Data

Scenario. In Exercise 8.5 you counted purchase requisitions just below $5,000, the lowest nonzero approval limit in the Employee table. The audit senior asks you to repeat the test with window functions, which can also find requisitions that look split: several consecutive requisitions for the same amount.

Requirements.

1. Calculate each requisition’s amount as Quantity times EstimatedUnitCost, rounded, and count the requisitions in each $50 band from $4,700 to $5,250, each band including its lower bound and excluding its upper one.

2. List the requisitions from $4,950.00 to $4,999.99 with their requester, approver, request date, and status.

3. Use LAG and LEAD, ordered by RequisitionID, to find requisitions whose amount equals both the one before and the one after. List each run of three.

4. For each run, report the requesters and approvers, and whether any requisition has no approver.

5. Write a short memo (one half page) to the audit senior on what the tests found and on the procedures you would perform next.

Deliverable. A script with the tests and a half-page memo.

11.16 Further Reading

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845 Its chapter on flexible budgets and direct-cost variances explains the direct labor efficiency variance, which compares the direct labor hours actually used with the hours allowed for the output produced, and its chapter on overhead variances explains where indirect costs appear. The direct part of this chapter’s monthly measure compares those two quantities for the whole plant month by month, without the standard rate that turns them into a variance, and the indirect part is a diagnostic measure, not an overhead variance.

Hyndman, R. J., & Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.). OTexts. https://otexts.com/fpp3/ A free online textbook on forecasting. Its section on moving averages (3.3) explains how an average over several periods smooths out short-term swings, although it centers its averages where a management report uses trailing ones, and its section on differencing (9.1) explains the seasonal difference, between an observation and the previous one from the same season, and shows that some series need a further difference as well.

Kimball, R., & Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/ The standard guide to organizing data for reporting and analysis. Its treatment of facts recorded at a chosen grain, what one row of a table represents, such as a month, and of summaries built on top of them, is the background to building measures like the one in this chapter as repeatable objects rather than one-off queries.

Loraas, T. M., & Searcy, D. L. (2010). Using queries to automate journal entry tests: Agile Machinery Group, Inc. Issues in Accounting Education, 25(1), 155–174. https://doi.org/10.2308/iace.2010.25.1.155 In this case, students act as an outsourced internal audit team and query nearly a million journal entry records of a real company. They compare the entries each module should produce with the entries actually made, and isolate entries for further study. It extends the journal entry tests of Exercise 11.5.

Molinaro, A., & de Graaf, R. (2020). SQL cookbook: Query solutions and techniques for all SQL users (2nd ed.). O’Reilly Media. A collection of solutions to common query problems, written for Oracle, DB2, SQL Server, MySQL, and PostgreSQL rather than SQLite. Its chapters on window functions, date arithmetic, and reporting show many variations on the techniques of this chapter, such as running totals, rankings, and pivoted reports, and translating them to SQLite is good practice.

Pencavel, J. (2015). The productivity of working hours. The Economic Journal, 125(589), 2052–2076. https://doi.org/10.1111/ecoj.12166 A study of the output and working hours of munition workers, most of them women. Below a threshold of hours, output rose in proportion to hours; above it, each additional hour added less output. It is useful background for reading a rising overtime share, because hours beyond the threshold may buy less output than the hours before them.

11.17 References

SQLite. (n.d.-a). CREATE VIEW. https://www.sqlite.org/lang_createview.html

SQLite. (n.d.-b). Date and time functions. https://www.sqlite.org/lang_datefunc.html

SQLite. (n.d.-c). Window functions. https://www.sqlite.org/windowfunctions.html

SQLite. (n.d.-d). The WITH clause. https://www.sqlite.org/lang_with.html