Running Total Sales by Date | Dataford Interview Questions

Your question is Running Total Sales by Date. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Arcesium QA needs to validate cumulative sales balances shown for active portfolios. Sales can have multiple posted records on the same settlement date, including corrections and null amounts.

Write a PostgreSQL query that calculates a running total of posted sales by settlement date for each active portfolio.

Requirements

Join portfolio_sales to portfolios and include only portfolios with portfolio_status = 'active'.
Include only sales with sale_status = 'posted'.
Aggregate sales to one row per portfolio and settlement date, treating a date containing only null amounts as zero.
Calculate the cumulative sales total per portfolio in chronological order.

Schema

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier.
portfolio_name	VARCHAR(100)	Human-readable Arcesium portfolio name.
portfolio_status	VARCHAR(20)	Current portfolio lifecycle status.

portfolio_sales

Column	Type	Description
sale_idPK	INT	Unique sales record identifier.
portfolio_id	INT	Portfolio associated with the sale.
settlement_date	DATE	Date on which the sale settled.
sale_amount	DECIMAL(14,2)	Signed monetary amount of the sale.
sale_status	VARCHAR(20)	Processing status of the sale record.