Module 08 Activity
Scenario
You are asked to compare the first half of June against the second half. Boundary handling is where this goes wrong, and it goes wrong quietly - a missing day looks exactly like a slow day.
Task
- Establish the true date span of the data, and how many days it covers.
- Split the month into two periods using half-open ranges (
>= start AND < end). - Report order count and revenue for each period.
- Prove no order was dropped or double-counted: the two period counts must sum to the table total.
- Repeat the split using
BETWEENon timestamps and record what changes.
Deliverable
A period comparison table with the boundaries written out explicitly, plus a one-line statement of your boundary convention, plus the result of the completeness check.
Check your work
The data covers 2026-06-01 to 2026-06-30 - 30 days, averaging 33.33 orders per day across 1,000 orders. Useful anchors while you test:
- The first three days hold 102 orders (
>= 2026-06-01 AND < 2026-06-04). - 30 June alone holds 33 orders.
Your two halves must sum to exactly 1,000. If they sum to 999, you lost a boundary day. If they sum to 1,001, a day is in both periods.
Step 5 is the point of the activity. BETWEEN '2026-06-01' AND '2026-06-15' on a TIMESTAMP column includes 15 June only up to 00:00:00 - and no order in this dataset was placed at exactly midnight, so all 33 orders placed on 15 June are silently excluded. BETWEEN returns 472; the half-open form >= '2026-06-01' AND < '2026-06-16' returns 505. A whole day vanished, and nothing in the output said so - the result still looked like a valid two-week figure.
The convention worth adopting permanently
Always write date ranges as >= start AND < next_start. It reads slightly worse and it is right every time, across every period length, with no special handling for the last day and no dependence on whether the column stores a date or a timestamp.