Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Three-day moving average of transactions

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.

Aggregate transactions to daily totals (transaction_date, daily_amount). Compute moving_avg_3d as the average of the current day and up to 2 preceding days (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), rounded to 2 decimals. Order by transaction_date.

Constraints

  • Round moving_avg_3d to 2 decimal places.

Examples

Input: date | daily_amount 2024-01-01 | 100 2024-01-02 | 50 2024-01-04 | 75 Output: transaction_date | daily_amount | moving_avg_3d 2024-01-01 | 100 | 100.0 2024-01-02 | 50 | 75.0 2024-01-04 | 75 | 75.0 Why this passes: The frame is row-based on existing days, not calendar gaps.

Topics: lakebench, sql, moving average, window.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

Three-day moving average of transactions

Interview-style drill: 3-day moving average over daily transaction totals.

Aggregate `transactions` to daily totals (`transaction_date`, `daily_amount`). Compute `moving_avg_3d` as the average of the current day and up to 2 preceding days (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), rounded to 2 decimals. Order by `transaction_date`.