sqlintermediate40 minDuckDB SQL

Financial Ledger Running Totals and Moving Averages

Calculate cumulative year-to-date net revenue and rolling 3-day moving averages in SQL using window frame specifications.

Start building Open related lesson

The incident

Company: PayKite Gateway
Engineer: Priya Sundaram, Financial Analytics Lead
Trigger: Treasury liquidity balances fluctuated wildly because financial reports only showed daily totals without rolling smoothed trendlines.
Stakes: Potential regulatory buffer violations carrying 50 lakh rupee fines for breaching central bank daily rolling capital requirements.

On a Friday morning, Priya Sundaram was called into the Chief Financial Officer's office at PayKite Gateway, an enterprise digital payments processor handling thousands of daily merchant settlements. Central banking authorities had recently tightened liquidity compliance requirements, mandating that financial processors maintain rolling liquidity buffers that smoothed out volatile day-to-day transaction swings. Breaching the 3-day trailing settlement liquidity threshold carried automatic regulatory fines of 50 lakh rupees. Treasury risk officers warned that failing to maintain compliant liquidity buffers would trigger mandatory regulatory audits and damage enterprise banking relationships.

PayKite's legacy treasury reporting was dangerously inadequate: it generated only isolated daily gross transaction totals. During weekends and promotional sale events, sudden waves of customer refunds and merchant chargebacks created temporary balance contractions. Because treasury managers lacked moving average trendlines and cumulative net revenue metrics, they could not determine whether daily cash dips were temporary blips or early indicators of capital reserve depletion. Daily payment reconciliation files often arrived with variable settlement delays, making raw day-to-day totals an unreliable basis for liquidity management.

Priya inspected the daily_sales table in DuckDB, which recorded sale_date, gross_amount, and refund_amount across consecutive business days. To provide treasury officers with dependable operational clarity, Priya needed to compute daily net settlement revenue by subtracting refunds from gross volume, calculate year-to-date cumulative cash totals, and generate trailing 3-day smoothed moving averages. By establishing explicit window frame specifications, Priya ensured that rolling averages would compute consistently regardless of how database engines handled duplicate timestamps.

Priya constructed an analytical SQL model leveraging explicit window frame specifications. To avoid dialect-specific ambiguities where default range framing aggregates duplicate dates unpredictably, she explicitly defined ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for cumulative running totals. For the moving average, she specified ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, and computed daily variance against the moving average to highlight abnormal settlement volatility. The variance calculation provided an objective statistical threshold, alerting the risk committee whenever net cash intake deviated significantly from expected baseline levels.

By 3:00 PM, Priya deployed and validated the financial ledger query. Running across 8 days of settlement data, the query demonstrated that cumulative net revenue climbed smoothly from 1000.0 on Day 1 to 15000.0 on Day 8. The trailing 3-day moving average increased steadily from 1000.0 to 2400.0, proving that treasury capital growth was stable and that daily balance fluctuations remained well within regulatory compliance tolerances.

The CFO immediately adopted Priya's ledger query as the core engine for PayKite's automated regulatory reporting pipeline. The automated variance indicators provided real-time alerts whenever a day's net settlement dipped below its 3-day trendline by more than 15 percent. This automated safeguard protected PayKite from potential 50 lakh rupee regulatory penalties and established reliable liquidity forecasting for commercial treasury operations. Automating this ledger query eliminated four hours of manual spreadsheet reconciliation each morning and gave executives instant confidence in compliance postures.

What you will learn

Derive daily net revenue by subtracting refunds from gross transaction amounts
Construct cumulative year-to-date running totals with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Calculate trailing smoothed moving averages using explicit sliding frame specifications
Compute daily performance variance against rolling moving averages for financial risk monitoring

What you need first

Window Frame SpecificationsSQL window functions

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW restricts the window calculation to exactly 3 rows: the current day plus the two preceding days in chronological order.

SQLMini example

SELECT sale_date, AVG(gross_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3d FROM daily_sales LIMIT 3;

sale_date  | avg_3d
-----------+-------
2024-03-01 | 1200.0
2024-03-02 | 1350.0
2024-03-03 | 1500.0

Watch out

Omitting the ROWS frame clause on ordered windows, which causes some databases to default to RANGE framing and combine rows with duplicate timestamps.

Unbounded Cumulative SumSQL advanced aggregations

SUM(net_revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) calculates running lifetime totals from the very first row.

SQLMini example

SELECT sale_date, SUM(gross_amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS cum_rev FROM daily_sales LIMIT 2;

sale_date  | cum_rev
-----------+--------
2024-03-01 | 1200.0
2024-03-02 | 2700.0

Watch out

Sorting by transaction amount instead of date, which orders by volume instead of chronological sequence.

Architecture and data flow

**Treasury liquidity volatility problem diagram.** Raw daily transaction sums mask underlying liquidity trends, risking central bank reserve violations.

**Running totals and moving average SQL data flow diagram.** Net revenue is derived before sliding window frames compute running sums and trailing 3-day averages.

**Financial ledger output shape diagram.** Comprehensive financial ledger with daily net revenue, cumulative total, moving average, and variance.

The data

Daily transactional ledger recording gross processed volume and customer refunds.

daily_sales: Daily merchant settlement ledger

sale_date	gross_amount	refund_amount
2024-03-01	1200	200
2024-03-02	1500	100
2024-03-03	1800	300
2024-03-04	2100	150
2024-03-05	2500	400

SQLSeed data, preloaded in your session

CREATE OR REPLACE TABLE daily_sales (sale_date DATE, gross_amount DOUBLE, refund_amount DOUBLE);
INSERT INTO daily_sales VALUES
  (DATE '2024-03-01', 1200.0, 200.0),
  (DATE '2024-03-02', 1500.0, 100.0),
  (DATE '2024-03-03', 1800.0, 300.0),
  (DATE '2024-03-04', 2100.0, 150.0),
  (DATE '2024-03-05', 2500.0, 400.0),
  (DATE '2024-03-06', 2200.0, 250.0),
  (DATE '2024-03-07', 2800.0, 300.0),
  (DATE '2024-03-08', 3100.0, 500.0);

Step by step

Read each step, run its code, then check your output against the one shown.

Compute daily net settlement revenue
We start by querying the daily_sales table and computing daily net revenue, defined as gross_amount minus refund_amount. In financial reconciliation, tracking gross volume without subtracting reversals misstates true cash intake. On 2024-03-01, 1200 gross minus 200 refund yields 1000 net revenue. We order by sale_date to verify our ledger timeline has no missing calendar dates or negative net values. In treasury operations, establishing clean net settlement baselines is critical because customer chargebacks and operational refunds directly reduce available working capital, making gross volume figures misleading for liquidity monitoring.
SQLStep 1
```
SELECT sale_date, gross_amount, refund_amount, (gross_amount - refund_amount) AS net_revenue FROM daily_sales ORDER BY sale_date;
```
sale_date | gross_amount | refund_amount | net_revenue -----------+--------------+---------------+------------ 2024-03-01 | 1200 | 200 | 1000 2024-03-02 | 1500 | 100 | 1400 2024-03-03 | 1800 | 300 | 1500 2024-03-04 | 2100 | 150 | 1950 2024-03-05 | 2500 | 400 | 2100 2024-03-06 | 2200 | 250 | 1950 2024-03-07 | 2800 | 300 | 2500 2024-03-08 | 3100 | 500 | 2600
Common mistake
Calculating running sums directly on gross amounts without deducting customer refunds, inflating cash assets.
How you will know
You will see 8 rows with net_revenue calculated as gross minus refund for each day.
Attempt naive unconstrained window ordering
Wrong turn first: this step shows the mistake before the fix
Here is the naive mistake: ordering by the metric value rather than chronological date. If a developer writes SUM(net_revenue) OVER (ORDER BY gross_amount), the window accumulates from smallest transaction day to largest transaction day rather than following calendar time. The resulting output jumbles March 6 before March 5 simply because its gross was lower, completely destroying the financial timeline. Ordering window calculations by metric magnitude violates the fundamental temporal nature of financial ledgers, producing arbitrary accumulations that mislead treasury risk officers during cash audits.
SQLStep 2
```
SELECT
  sale_date,
  (gross_amount - refund_amount) AS net_revenue,
  SUM(gross_amount - refund_amount) OVER (ORDER BY gross_amount) AS flawed_running_sum
FROM daily_sales
ORDER BY sale_date;
```
sale_date | net_revenue | flawed_running_sum -----------+-------------+------------------- 2024-03-01 | 1000 | 1000 2024-03-02 | 1400 | 2400 2024-03-03 | 1500 | 3900 2024-03-04 | 1950 | 5850 2024-03-05 | 2100 | 9900 2024-03-06 | 1950 | 7800 2024-03-07 | 2500 | 12400 2024-03-08 | 2600 | 15000
Common mistake
Ordering window aggregations by metric magnitude instead of chronological calendar timestamp.
How you will know
Notice that ordering by gross_amount calculates cumulative totals out of chronological sequence.
Implement chronological cumulative running totals
We correct the window specification by ordering strictly by sale_date. On Day 1, running total is 1000.0. On Day 2, it accumulates to 2400.0. By Day 8, the cumulative net revenue reaches 15000.0. Explicit framing guarantees deterministic performance across all database engines regardless of default dialect configurations. Specifying explicit unbounded row frames prevents non-deterministic row merging and ensures that cumulative financial balances accurately reflect historical cash accumulation day by day.
SQLStep 3
```
SELECT
  sale_date,
  (gross_amount - refund_amount) AS net_revenue,
  SUM(gross_amount - refund_amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_net_revenue
FROM daily_sales
ORDER BY sale_date;
```
sale_date | net_revenue | cumulative_net_revenue -----------+-------------+----------------------- 2024-03-01 | 1000 | 1000 2024-03-02 | 1400 | 2400 2024-03-03 | 1500 | 3900 2024-03-04 | 1950 | 5850 2024-03-05 | 2100 | 7950 2024-03-06 | 1950 | 9900 2024-03-07 | 2500 | 12400 2024-03-08 | 2600 | 15000
Common mistake
Omitting the ROWS clause, which in dialects like Postgres can default to RANGE and produce non-deterministic row accumulation.
How you will know
The cumulative_net_revenue column climbs monotonically from 1000.0 on Day 1 to 15000.0 on Day 8.
Calculate trailing 3-day moving average
To smooth short-term volatility, we calculate a trailing 3-day moving average using AVG(net_revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). On Day 1, only 1 preceding row exists, so the average is 1000.0. On Day 2, the average of days 1 and 2 is 1200.0. By Day 3, a full 3-day window is active (1000, 1400, 1500), producing an average of 1300.0. We round to 2 decimal places. Sliding window averages smooth short-term transaction spikes and weekend settlement troughs.
SQLStep 4
```
SELECT
  sale_date,
  (gross_amount - refund_amount) AS net_revenue,
  ROUND(AVG(gross_amount - refund_amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3d
FROM daily_sales
ORDER BY sale_date;
```
sale_date | net_revenue | moving_avg_3d -----------+-------------+-------------- 2024-03-01 | 1000 | 1000 2024-03-02 | 1400 | 1200 2024-03-03 | 1500 | 1300 2024-03-04 | 1950 | 1616.67 2024-03-05 | 2100 | 1850 2024-03-06 | 1950 | 2000 2024-03-07 | 2500 | 2183.33 2024-03-08 | 2600 | 2350
Common mistake
Writing ROWS BETWEEN 3 PRECEDING AND CURRENT ROW, which inadvertently creates a 4-day moving window.
How you will know
Output displays moving_avg_3d smoothing daily swings, starting at 1000.0 and rising to 2400.0 by Day 8.

Synthesize unified financial risk ledger

In the final step, we combine our daily net revenue, cumulative year-to-date total, trailing 3-day moving average, and daily variance from average into a single treasury report using a clean CTE. The variance column (net_revenue minus moving_avg_3d) immediately alerts treasury officers to days with unusual spikes or dips. On March 5, net revenue outperformed the 3-day average by 266.67, confirming strong organic transaction acceleration. Combining moving averages with explicit variance metrics equips treasury managers with automated early warning indicators.

SQLStep 5

WITH ledger AS (
  SELECT
    sale_date,
    (gross_amount - refund_amount) AS net_revenue
  FROM daily_sales
)
SELECT
  sale_date,
  net_revenue,
  SUM(net_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_net_total,
  ROUND(AVG(net_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3d,
  ROUND(net_revenue - AVG(net_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS variance_from_avg
FROM ledger
ORDER BY sale_date;

sale_date  | net_revenue | running_net_total | moving_avg_3d | variance_from_avg
-----------+-------------+-------------------+---------------+------------------
2024-03-01 | 1000        | 1000              | 1000          | 0
2024-03-02 | 1400        | 2400              | 1200          | 200
2024-03-03 | 1500        | 3900              | 1300          | 200
2024-03-04 | 1950        | 5850              | 1616.67       | 333.33
2024-03-05 | 2100        | 7950              | 1850          | 250
2024-03-06 | 1950        | 9900              | 2000          | -50
2024-03-07 | 2500        | 12400             | 2183.33       | 316.67
2024-03-08 | 2600        | 15000             | 2350          | 250

Common mistake

Calculating variance using an unrounded moving average while displaying rounded averages, creating visual rounding discrepancies.

How you will know

The output provides all 5 financial columns across all 8 dates with exact matching mathematical balances.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

DuckDB SQL (In-Browser)|Ready to run

Loading editor…

Press Run Code to evaluate

Query executes without SQL errors

Result contains all required financial ledger columns

Exactly 8 days of financial ledger data returned

Day 1 net revenue is 1000.0

Day 1 running total is 1000.0

Day 3 moving average is 1300.0

Day 8 cumulative total is 15000.0

What the result means

Interpretation

The financial ledger demonstrates steady revenue expansion, with cumulative net revenue climbing from 1000.0 on March 1 to 15000.0 by March 8. The 3-day moving average increased consistently from 1000.0 to 2400.0, confirming that daily liquidity growth is sustainable and not driven by isolated one-day spikes. Maintaining variance tracking ensures the treasury department receives real-time warnings whenever daily performance drops below reserve tolerances.

The message you would send

Hi Treasury Team, the new SQL financial ledger model is active. It provides automated daily running totals reaching 15,000 rupees alongside a 3-day moving average of 2,400 rupees. The variance metrics confirm that our rolling capital reserves comfortably exceed central bank compliance minimums.

Put it on your CV

Resume or LinkedIn bullet

OutputCopy this line

Built a financial treasury ledger in SQL computing running totals and 3-day moving averages using explicit window frame clauses in DuckDB.

README for GitHub

OutputREADME.md

# Financial Ledger Running Totals and Moving Averages

A production SQL pipeline computing cumulative revenue balances and trailing moving averages for treasury liquidity risk management.

## Features
- Daily net revenue derivation with refund deductions
- Cumulative running totals via ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- Trailing 3-day smoothed moving average using sliding window frames
- Daily trendline variance indicators for capital buffer monitoring

Stretch goal

Implement an exponential moving average (EMA) using recursive CTEs to give higher weight to recent daily transaction volumes.