Brightlane's billing system generates day sequences for contract periods. A developer is testing what generate_series produces when the end date precedes the start date — a condition that can occur if a contract record has its dates inverted.
Write a query to return the total number of rows produced when generate_series is called with a start of '2024-03-01', an end of '2024-02-01', and a step of 1 day.
Assumptions:
- For these inputs,
generate_seriesproduces an empty result with no error raised. - Wrapping the empty result in
COUNT(*)yields a single row with the count0.
Output:
- A single row with one column,
row_count, containing the integer0.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
COUNT(*) AS row_count
FROM
(
SELECT
GENERATE_SERIES('2024-03-01'::date, '2024-02-01'::date, INTERVAL '1 day')::date AS DAY
) gsThe shape
generate_series walks forward from the start by the step until the value exceeds the end. When the end already precedes the start and the step is positive, the very first comparison fails and the function emits no rows. Wrapping that empty set in a derived table and counting it returns a single row with row_count = 0.
Clause by clause
- The inner
SELECT generate_series('2024-03-01'::date, '2024-02-01'::date, interval '1 day')::date AS daycalls the function with a start that is one month past the end. The first candidate value is'2024-03-01', which already exceeds the end of'2024-02-01', so the function returns immediately with zero rows. No error is raised; the result is simply an empty set. - Wrapping that empty inner query in
( ... ) gsturns the zero-row result into a derived table the outer query can read from. SELECT COUNT(*) AS row_count FROM (...) gsaggregates over the empty derived table.COUNT(*)over zero rows returns0, and aggregates always produce exactly one row even when the input is empty, so the outer query returns a single row with the count0.
Why this and not relying on generate_series to error or produce a backwards range
generate_series does not error on an inverted range with a positive step. It also does not silently flip the bounds and walk backward; that would require a negative step. The function takes the comparison literally: start, step forward, stop when the next candidate would exceed the end. With start > end and a positive step, "the next candidate would exceed the end" is true at iteration zero, so the loop never enters its body. A negative step like interval '-1 day' is what walks the range in reverse.
The trap
The behaviour is silent. There is no warning that the inputs are inverted, and a downstream query that reports "zero rows in range" looks identical whether the data legitimately had no activity or the date arguments were swapped by mistake. On any pipeline where the start and end come from another query or a parameter, validate that start <= end before calling generate_series, or check that the row count matches the expected length of the window. A zero-row spine produces a zero-row report, which is rarely what the analyst meant.
You practiced verifying generate_series boundary behavior — when the end precedes the start with a positive step, the function returns zero rows rather than failing or producing a degenerate range.
