Write a SQL query that returns every active customer with their paid-order count and total for a requested period, including customers whose total is zero.

Tables: Customers(CustomerId, Name, IsActive) and Orders(OrderId, CustomerId, Status, PaidAt, TotalAmount). Return CustomerId, Name, PaidOrderCount, and PaidTotal for [FromUtc, ToUtc). Every active customer must appear even when no matching paid order exists.

Ada has two paid orders worth 40 and 60 in the period, so her row contains count 2 and total 100. Lin is active but has no paid order in the period, so Lin still appears with count 0 and total 0.

• Use @FromUtc inclusive and @ToUtc exclusive.
• Only Status = 'Paid' contributes to the count and total.
• Inactive customers do not appear.
• Do not turn the left join into an inner join by filtering order rows in WHERE.

I start from Customers because that is the set the result promises to preserve. Conditions that decide whether an order contributes belong in the join predicate. I then group by the customer columns and convert the null sum produced by no matches to zero. COUNT(o.OrderId) returns zero for an unmatched customer because the joined order id is null.

SELECT
    c.CustomerId,
    c.Name,
    COUNT_BIG(o.OrderId) AS PaidOrderCount,
    COALESCE(SUM(o.TotalAmount), CONVERT(decimal(18, 2), 0)) AS PaidTotal
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId
   AND o.Status = 'Paid'
   AND o.PaidAt >= @FromUtc
   AND o.PaidAt < @ToUtc
WHERE c.IsActive = 1
GROUP BY c.CustomerId, c.Name
ORDER BY c.CustomerId;

The database can seek matching order rows efficiently with an index beginning with CustomerId and then the filtering columns, or with a suitable filtered index for paid orders. The grouping work grows with the matching order rows, while the customer scan determines how many zero-total rows must still be returned.

• A customer with only pending or cancelled orders.
• Orders exactly at @FromUtc and @ToUtc.
• A customer with no order rows at all.
• A nullable TotalAmount if the schema permits it.

• Include an active customer with no matching orders to catch an accidental inner join.
• Place one paid order exactly on each boundary to verify the half-open range.
• Mix paid and unpaid orders for the same customer.

• Putting o.Status or o.PaidAt in WHERE and removing zero-order customers.
• Using COUNT(*), which returns one for the null-extended row.
• Returning a null total instead of zero.
• Using an inclusive end timestamp and double-counting boundary records.