I asked DeepSeek for a PostgreSQL query that lists every customer and counts their orders placed on or after January 1, 2026. Customers with no qualifying orders should still appear with a count of zero.

This is the query I’m checking:

SELECT
    c.id,
    c.name,
    COUNT(o.id) AS order_count
FROM customers AS c
-- Keep customers in the join even when no order matches.
LEFT JOIN orders AS o
    ON o.customer_id = c.id
-- Restrict the report to orders from the requested period.
WHERE o.created_at >= DATE '2026-01-01'
GROUP BY c.id, c.name;

In my three-customer test, one customer has a recent order, one has only an older order, and one has never ordered. Only the first customer appears, although I expected three rows.

I thought the LEFT JOIN filtering behavior would preserve all customers. Is the date condition changing that, and does it matter whether the condition belongs in ON or WHERE?

I’d also like to understand COUNT with unmatched rows. If I get DeepSeek to revise the query, should I keep COUNT(o.id), or would COUNT(*) produce the same zero-order result? I’m looking for the reasoning so I can review similar generated queries myself.