Compute a Rolling Seven-Day Revenue Sum with Missing Dates

Problem Statement

You are working on Amazon's Marketplace Analytics team. The team wants to monitor seller revenue trajectories by tracking daily sales and a rolling 7-day revenue sum for each seller.

You are provided with an orders table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    seller_id VARCHAR(50) NOT NULL,
    order_timestamp TIMESTAMPTZ NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    order_amount NUMERIC(10, 2) NOT NULL
);

Write a PostgreSQL query that calculates the daily revenue and rolling 7-day revenue for each seller under the following rules:

1. Status filter: Include only orders where order_status = 'completed'.
2. Time zone: Truncate all timestamps to the UTC calendar date (order_date).
3. Continuous date spine: For each seller, generate a complete daily sequence of dates starting from the date of their first completed order up to the date of their last completed order (inclusive). If a seller had no completed orders on a given date, output 0.00 for daily_revenue.
4. Rolling 7-day window: Compute rolling_7d_revenue as the sum of daily_revenue for the current date and the preceding 6 calendar days. If fewer than 6 preceding days exist in the seller's history, sum all available days up to that point.
5. Format & Ordering: Round both daily_revenue and rolling_7d_revenue to 2 decimal places. Sort the output by seller_id ASC, order_date ASC.

Example

Input: orders

Output

Explanation: 2024-03-02 had no completed orders, so daily_revenue is 0.00. The rolling 7-day sum on 2024-03-04 covers March 1 through March 4 (100.00 + 0.00 + 200.00 + 50.00 = 350.00).