Unit 08.02: Comparing periods of unequal length

Unit ID: SQL-M08-U03 - Estimated active time: 14-17 minutes Objective: make a period comparison fair, and refuse to publish one that is not.

The most common reporting lie is accidental

"Revenue is down 72% this week." Look again at the weekly table:

2026-06-22 | 231
2026-06-29 |  66

The final week contains two days, not seven. Comparing it to a full week is meaningless, and the number will be read as a business collapse.

This is the single most frequent error in period reporting, and nothing in the query flags it.

A fair comparison

Compare equal spans. June splits cleanly into two 15-day halves:

SELECT
  SUM(CASE WHEN placed_at <  '2026-06-16' THEN 1 ELSE 0 END) AS first_half,
  SUM(CASE WHEN placed_at >= '2026-06-16' THEN 1 ELSE 0 END) AS second_half
FROM orders;
-- 505 | 495

SELECT
  ROUND(SUM(CASE WHEN placed_at <  '2026-06-16' THEN order_total ELSE 0 END), 2) AS first_half,
  ROUND(SUM(CASE WHEN placed_at >= '2026-06-16' THEN order_total ELSE 0 END), 2) AS second_half
FROM orders;
-- 1341555.00 | 1359908.00

Orders fell slightly (505 → 495) while revenue rose slightly (₹13.42L → ₹13.60L). Both halves are 15 days, so the comparison means something: fewer orders, higher average value.

When periods cannot be equal

Sometimes the current period is genuinely incomplete. Then either:

1. Compare like for like - first 15 days of this month against first 15 of last month; or
2. Label it clearly - "month to date (15 of 30 days)".

What you must not do is show an incomplete period beside complete ones without saying so.

The check to run before publishing any period comparison

SELECT
  MIN(placed_at) AS period_start,
  MAX(placed_at) AS period_end,
  COUNT(DISTINCT CAST(placed_at AS DATE)) AS days_with_data
FROM orders
WHERE placed_at >= '2026-06-01' AND placed_at < '2026-07-01';
-- 2026-06-01 10:00:00 | 2026-06-30 21:00:00 | 30

30 days with data confirms the period is complete. Had it returned 2, the "72% drop" would have explained itself before it reached anyone.

Practice

A dashboard shows this week down 60% against last week. List three things you check before believing it.

Takeaway

Count the days in each period before comparing them. A partial period beside a complete one is a manufactured trend.

---

Mark complete