Counting Customers in a Date Range

SQLDate rangesJuniorMidUpdated Nov 10, 2026

How many different customers placed at least one order in October 2026? placed_at is a timestamp, so orders land at any time of day, including late on October 31.

Schema

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (d)

SELECT COUNT(DISTINCT customer_id)
FROM orders
WHERE placed_at >= '2026-10-01' AND placed_at < '2026-11-01';

COUNT(DISTINCT customer_id) counts each customer once, and the half-open range from October 1 up to (but excluding) November 1 covers every moment of October. (a) and (c) count orders: COUNT(customer_id) only skips NULLs, so a repeat customer is still counted once per order. (b) counts the right thing over the wrong range: '2026-10-31' means midnight at the start of October 31, so BETWEEN misses everything placed later that day.

Going Deeper

With TIMESTAMPTZ, a literal like '2026-10-01' is read in the session's time zone, so set it (SET TIME ZONE 'UTC') or write the offset ('2026-10-01 00:00+00') when October means a specific zone. The half-open pattern keeps working if the column ever changes between DATE and TIMESTAMPTZ.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT COUNT(*)
FROM orders
WHERE placed_at >= '2026-10-01' AND placed_at < '2026-11-01';

Option (b)

SELECT COUNT(DISTINCT customer_id)
FROM orders
WHERE placed_at BETWEEN '2026-10-01' AND '2026-10-31';

Option (c)

SELECT COUNT(customer_id)
FROM orders
WHERE placed_at >= '2026-10-01' AND placed_at < '2026-11-01';