dbdbexercises
Practice Free
← Syllabus|
Medium

16. Day-over-Day Account Balance Net Delta

MediumWindow FunctionsPostgreSQL 16PRO
Table: daily_account_snapshots
+-----------------+------------+
| Column Name     | Type       |
+-----------------+------------+
| account_id      | int        |
| snapshot_date   | date       |
| closing_balance | numeric(12 |
+-----------------+------------+

account_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Treasury risk operations track daily closing cash positions across merchant accounts, computing the net dollar change compared to the previous day's closing balance.

For each record in `daily_account_snapshots`, use `LAG()` to find the previous recorded balance for that account. Compute `daily_net_change` as `(closing_balance - prev_closing_balance)`. Return: 1. `account_id` 2. `snapshot_date` 3. `closing_balance` 4. `daily_net_change` Order results by `account_id` ascending, then by `snapshot_date` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Daily closing balances with day-over-day net cash variance.

Constraints:
  • Query must utilize clause(s): LAGOVERPARTITION BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Day-over-Day Account Balance Net Delta” SQL Problem

Business Scenario

Treasury risk operations track daily closing cash positions across merchant accounts, computing the net dollar change compared to the previous day's closing balance.

Expected Output & Criteria

Daily closing balances with day-over-day net cash variance.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE daily_account_snapshots (
        account_id INT NOT NULL,
        snapshot_date DATE NOT NULL,
        closing_balance NUMERIC(12, 2) NOT NULL,
        PRIMARY KEY (account_id, snapshot_date)
      );

Target SQL Clauses & Operators

LAGOVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 18 exercises and syntax guides.

View Window Functions Track