Write a SQL query that returns daily order counts, paid counts, cancelled counts, and paid revenue for a date range.

Table: Orders(OrderId, CreatedAtUtc, Status, TotalAmount). Return one row per UTC calendar date in [FromUtc, ToUtc): total orders, paid orders, cancelled orders, and revenue from paid orders. Dates with no orders do not need a row.

For a day with two paid orders worth 25 and 75 plus one cancelled order, return total 3, paid 2, cancelled 1, and paid revenue 100.

• Filter the timestamp with a half-open range before grouping.
• Only paid orders contribute to revenue.
• Keep count and money types large enough for production data.
• Make the reporting time zone explicit; this exercise uses UTC dates.

I filter on the raw timestamp so the range can use an index, then convert the remaining rows to a UTC date for grouping. Each KPI gets its own CASE expression, which keeps the business definition visible. ELSE 0 prevents null arithmetic and makes days containing no paid orders return zero revenue.

SELECT
    CONVERT(date, o.CreatedAtUtc) AS OrderDate,
    COUNT_BIG(*) AS TotalOrders,
    SUM(CASE WHEN o.Status = 'Paid' THEN CONVERT(bigint, 1) ELSE 0 END) AS PaidOrders,
    SUM(CASE WHEN o.Status = 'Cancelled' THEN CONVERT(bigint, 1) ELSE 0 END) AS CancelledOrders,
    SUM(CASE
            WHEN o.Status = 'Paid' THEN o.TotalAmount
            ELSE CONVERT(decimal(18, 2), 0)
        END) AS PaidRevenue
FROM dbo.Orders AS o
WHERE o.CreatedAtUtc >= @FromUtc
  AND o.CreatedAtUtc < @ToUtc
GROUP BY CONVERT(date, o.CreatedAtUtc)
ORDER BY OrderDate;

The range predicate can seek on CreatedAtUtc; conversion happens only after candidate rows are found. A very large dashboard workload may still justify a daily summary table, especially when the same historical dates are recalculated repeatedly.

• A day with orders but no paid orders.
• An order exactly at the end boundary.
• A status outside the three reported categories.
• Large counts that exceed int.

• Include a pending order and verify it affects only total count.
• Use no paid rows on a returned day.
• Place records on both range boundaries.

• Counting all order amounts as revenue.
• Applying a date conversion in the range predicate.
• Leaving the reporting time zone undefined.
• Using one status count as if it represented all orders.