Write a SQL query that returns paid revenue and rolling seven-day revenue for every calendar date in a requested range.

If Monday has 10 revenue, Tuesday has no orders, and Wednesday has 30, the daily rows are 10, 0, and 30. Wednesday's rolling value includes all three dates and equals 40.

• Return dates with no orders as zero revenue.
• Load six days before @FromDate so the first displayed rolling value has full history.
• Only paid orders contribute.
• Use UTC calendar dates for this exercise.

I first aggregate orders to daily grain. Then I join those totals onto a complete calendar series so a ROWS frame really means seven consecutive calendar days rather than seven dates that happened to contain orders. I calculate the window over the expanded range and filter display dates only after the window value exists.

WITH RevenueByDay AS
(
    SELECT
        CONVERT(date, o.PaidAtUtc) AS RevenueDate,
        SUM(o.TotalAmount) AS DailyRevenue
    FROM dbo.Orders AS o
    WHERE o.Status = 'Paid'
      AND o.PaidAtUtc >= DATEADD(day, -6, CONVERT(datetime2, @FromDate))
      AND o.PaidAtUtc < DATEADD(day, 1, CONVERT(datetime2, @ThroughDate))
    GROUP BY CONVERT(date, o.PaidAtUtc)
),
Series AS
(
    SELECT
        c.DateValue,
        COALESCE(r.DailyRevenue, CONVERT(decimal(18, 2), 0)) AS DailyRevenue
    FROM dbo.CalendarDates AS c
    LEFT JOIN RevenueByDay AS r ON r.RevenueDate = c.DateValue
    WHERE c.DateValue >= DATEADD(day, -6, @FromDate)
      AND c.DateValue <= @ThroughDate
),
Rolling AS
(
    SELECT
        DateValue,
        DailyRevenue,
        SUM(DailyRevenue) OVER
        (
            ORDER BY DateValue
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS RollingSevenDayRevenue
    FROM Series
)
SELECT DateValue, DailyRevenue, RollingSevenDayRevenue
FROM Rolling
WHERE DateValue >= @FromDate
ORDER BY DateValue;

The expensive step is the initial order aggregation over the expanded date range. A filtered or covering index for paid orders can help, while a persisted daily summary makes repeated dashboard reads much cheaper. The window runs over a small date series rather than raw order rows.

• Several consecutive dates with no orders.
• The first requested date needs six prior days of history.
• A requested range shorter than seven days.
• No paid orders anywhere in the expanded range.

• Remove one date from the order data and verify the calendar still returns it.
• Put revenue exactly six and seven days before a displayed date.
• Verify the first displayed date uses pre-range history.
• Test an all-zero range.

• Applying the window directly to order rows instead of daily totals.
• Using seven populated dates instead of seven calendar dates.
• Filtering to @FromDate before calculating the rolling value.
• Recomputing years of order history for a small report range.