Splitting Date Ranges by Month

Concepts covered: Inequality Joins, Join Cardinality, Date Differences, Aggregate Window Fns, Array Flattening

This question arrives without the word range. "Revenue is recognized per night stayed; give me monthly revenue." "Subscriptions start on any day of the month; what is MRR by month?" "Leave requests have a first and last day; how many fall in each payroll month?" Every row has a start and an end, and the answer buckets by month. Grouping by DATE_TRUNC('month', check_in) books the whole stay to the month it began, which is how a revenue dashboard drifts above the ledger without a single wrong row.

Exploding versus joining

Explode to nights, then group

• A row per night via generate_series, or sequence plus UNNEST
• Any bucket afterwards: month, fiscal period, ISO week
• Cost is ranges times length: 30M stays at 4 nights is 120M rows
• Breaks on long ranges: 2M annual plans become 730M rows

Overlap join to a month table

• A row per range per month it touches
• Clipping arithmetic with GREATEST and LEAST
• Output stays close to input: most stays touch 1 or 2 months
• Needs a small table of months, which the calendar supplies

-- Explode (Trino): a row per night, then any bucket
SELECT DATE_TRUNC('month', n.night) AS month_start,
       COUNT(*) AS nights
FROM stays s
CROSS JOIN UNNEST(
  sequence(s.check_in, s.check_out - INTERVAL '1' DAY)
) AS n(night)
GROUP BY 1;

Both work, and the interviewer grades whether you name the cost. Explode when ranges are short and the next step needs daily grain anyway. Use the overlap join when ranges run long, or when the output only needs months.

The overlap join, row by row

A range overlaps a month when it starts before the month ends and ends after the month starts. With both sides half-open (check-out is the morning the guest leaves, so that night is not stayed), the test is 2 strict comparisons, and the nights in each month are the clipped end minus the clipped start.

SELECT s.stay_id, m.month_start, DATE_DIFF('day',
  CAST(GREATEST(s.check_in, m.month_start) AS DATE),
  CAST(LEAST(s.check_out, m.next_month) AS DATE)) AS nights
FROM stays s JOIN months m
  ON m.month_start < s.check_out AND m.next_month > s.check_in

Stay 102 runs from March 29 to the morning of April 3 and passes both comparisons against both months, so it becomes 2 rows: 3 nights in March and 2 in April. Its $600 becomes $360 and $240, where grouping by check-in month put all $600 in March. The slices add back to its 5 nights, and that check is worth saying aloud: the slices of a range must sum to the range's length. A stay from March 4 to March 9 fails April's first comparison, since April 1 is not before March 9, and stays a single row. A stay from February 27 to April 2 passes the same 2 comparisons in 3 months. February gets 3 nights of it, because 2024 has a February 29.

The convention to say aloud

Hotel nights are half-open by nature. Subscriptions often are not: a plan "ending 2024-03-31" usually includes the 31st. Feed an inclusive end into the same arithmetic and every range loses its last day, a 1-day error per subscription that adds up to a visible MRR gap. Do the conversion to half-open once with end_date + INTERVAL '1' DAY where the data enters, then tell the interviewer you did.

Allocating the money to the cent

Revenue per slice is amount times nights over total nights, and rounding each slice on its own leaks cents: $100.00 across 3 single-night slices rounds to 33.33 in each slice and loses a cent. Taking differences of rounded cumulative amounts makes the slices sum to the original exactly.

WITH slices AS (
  SELECT s.stay_id, CAST(s.amount AS DECIMAL(18,2)) AS amount,
         m.month_start,
         DATE_DIFF('day', GREATEST(s.check_in, m.month_start),
                          LEAST(s.check_out, m.next_month)) AS nights,
         DATE_DIFF('day', s.check_in, s.check_out) AS total_nights
  FROM stays s
  JOIN months m
    ON m.month_start < s.check_out
   AND m.next_month  > s.check_in
),
running AS (
  SELECT *, SUM(nights) OVER (PARTITION BY stay_id
                              ORDER BY month_start) AS cum_nights
  FROM slices
)
SELECT stay_id, month_start, nights,
       ROUND(amount * cum_nights / total_nights, 2)
     - ROUND(amount * (cum_nights - nights) / total_nights, 2) AS revenue
FROM running;

For the $100.00 case, the running totals after each night round to 33.33, then 66.67, then 100.00, so the slices are 33.33, 33.34 and 33.33. Finance reconciles to the cent, and an allocation that drifts a cent per booking turns into a reconciliation ticket every month.

The usual follow-up swaps the bucket: "now by fiscal period," or "now by ISO week." Nothing in the join is specific to months. Any table of half-open periods with a start and a next start works, which is the argument for drawing the periods from the calendar table instead of generating months inline in each query.

About this lesson

This section is part of the Date Arithmetic: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.