Comparing Two Time Periods in a CSV Without Being Fooled

"How are we doing compared to last month?" is the question every dashboard answers and most answer misleadingly. The arithmetic is trivial, this period minus the last one over the last one. What goes wrong is that the two periods are not comparable: one is a day shorter, one is not finished, one has an extra weekend, one contains a holiday. Each of those produces a change that is real as arithmetic and false as information. This article builds a period comparison in SQL on a CSV, using a 2 million row orders file, and shows each trap with the numbers it produced. It is the second move in more analysis moves for CSV data, and it leans on the date handling in calculating month-over-month growth.

Totals by month, and the trap in them

The obvious query groups by month:

SELECT date_trunc('month', created_at) AS month, sum(amount) AS revenue,
       count(DISTINCT created_at::DATE) AS days
FROM read_csv('orders-2m.csv') GROUP BY 1 ORDER BY 1;

Add the change from the previous month with lag, and a per-day figure:

WITH m AS (
  SELECT date_trunc('month', created_at) AS month, sum(amount) AS revenue,
         count(DISTINCT created_at::DATE) AS days
  FROM read_csv('orders-2m.csv') GROUP BY 1)
SELECT strftime(month, '%Y-%m') AS month, round(revenue) AS revenue, days,
       round(100 * (revenue / lag(revenue) OVER (ORDER BY month) - 1), 1) AS mom_pct,
       round(revenue / days) AS per_day
FROM m ORDER BY month;

month    revenue     days  mom_pct  per_day
2025-01  13610354.0  31    NULL     439044.0
2025-02  12356780.0  28    -9.2     441314.0
2025-03  13608069.0  31    10.1     438970.0
2025-04  13136102.0  30    -3.5     437870.0

Read the third column against the fourth. February fell 9.2 percent against January, March rose 10.1 percent and April fell 3.5 percent. Those are the headlines, and they track the number of days in the month: 28, 31, 30. Per day, the four months are within 0.8 percent of each other, and February was the best of them. If you report the monthly totals, someone will ask why February was bad, and the honest answer is that it has fewer days.

Two normalizations are common. Revenue per day, as above, divides by the number of days with data. Note that count(DISTINCT created_at::DATE) counts days that have at least one order, so a closed day lowers the count and raises the per-day figure; if closed days should count, divide by the calendar days in the month instead. The second is to compare the same number of days: the first 28 days of each month, or a rolling 28-day window ending on the same day-of-month.

Half-open intervals

When you write the interval yourself, write it as >= start AND < next_start, and never use BETWEEN on timestamps:

SELECT sum(amount) FROM read_csv('orders-2m.csv')
WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';

BETWEEN '2025-02-01' AND '2025-02-28' compares the timestamp with midnight at the start of the 28th, so every order after 00:00:00 on 28 February is excluded. On the test file the two forms gave 11,926,337.79 for BETWEEN and 12,356,780.42 for the half-open interval, a difference of 430,442.63, or 3.5 percent of the month, lost without any error. and BETWEEN '2025-02-01' AND '2025-02-28 23:59:59' loses the last second, and fractional seconds. The half-open form has no such edge: each instant is in exactly one period, adjacent periods share a boundary without overlap and the length of a period is the difference between its bounds. date_trunc('month', created_at) produces the same result without writing dates, and is what the grouped query above uses.

The open period

The current month is not finished, and a comparison that includes it shows a drop that is just the calendar. If it is the 18th and you compare this month to date with all of last month, you are comparing 18 days to 31. Three honest options: leave the open period out, compare it with the same days of the previous period (this month's days 1 to 18 against last month's days 1 to 18) or show a per-day rate with the number of days beside it. The month-to-date against same-days comparison is the most useful, and the query needs the day of the month of the latest row:

WITH bounds AS (SELECT max(created_at)::DATE AS last_day FROM read_csv('orders-2m.csv'))
SELECT
  sum(amount) FILTER (WHERE created_at >= date_trunc('month', last_day)
                      AND created_at::DATE <= last_day) AS this_month_to_date,
  sum(amount) FILTER (WHERE created_at >= date_trunc('month', last_day) - INTERVAL 1 MONTH
                      AND created_at::DATE <= last_day - INTERVAL 1 MONTH) AS last_month_same_days
FROM read_csv('orders-2m.csv'), bounds;

On the test file, whose last day is 31 December, it gave 13,607,962.85 for December and 13,152,024.05 for the same days of November (1 to 30), a 3.5 percent rise on a like-for-like basis. The last_day - INTERVAL 1 MONTH expression handles the month length by clamping: from 31 March it gives 28 February, which is a reminder to look at the edge cases. Also be careful that the data itself is complete up to the last day. An export taken in the middle of the day has a partial last day, and the last day should be excluded or treated as incomplete too.

The weekday mix

If the business has a weekly rhythm (detecting seasonality in a CSV of daily sales), two periods with different weekday counts are not comparable. A 30-day month has either four or five of some weekdays, and a month with five Saturdays has more Saturday revenue. The remedies: compare the same number of full weeks (28 days against 28 days), compare each weekday against the same weekday and average the ratios, or use a seven-day moving average at the endpoints. For businesses with a strong weekend effect, the difference between a month with five Fridays and one with five Mondays can be several percent, which is larger than many of the changes people are trying to detect.

Year over year, and holidays

When seasonality is annual, the right comparison is with the same period a year earlier and not with the previous one: December against November tells you about Christmas, and December against last December tells you about the business. Align on calendar position as well as date, because 1 to 28 February this year has the same weekdays as the same days a year ago only in non-leap years that start on a different weekday. A rule that works: compare 52 weeks back (364 days), so the weekday mix is identical, and treat moving holidays (Easter, Lunar New Year) as known disturbances to flag, not to average away.

Say how big the base is

A change in percent without its base is a rumor. Revenue up 20 percent could be 20 percent of 40 orders or of 4,000,000, and the first is within the noise of a single large customer. Show the two values and the count of orders next to the change, and for small bases, say that the change is within what chance would produce. A rough guide: the standard error of an order count is its square root, so a change of 8 on a base of 40 is about 1.3 standard errors and not distinguishable from noise, while a change of 8,000 on 4,000,000 is about 4 standard errors. Detecting sudden drops in a CSV metric takes the noise question further.

The composition matters too: revenue per order may be flat while the number of orders moves, or the other way round. Split the change into volume and average order value so that the explanation of a rise or fall is a statement about customers and not a single number. In the test file, quarterly orders ran 494,145, 498,993, 503,031 and 503,831 with an average order value of 80.09, 80.03, 80.02 and 80.21, so the revenue growth over the year came from order count and the value per order did not move.

When a comparison needs more than two numbers

Two numbers are a snapshot. When the question is "is this a trend or noise", you need the series: a line over the last 12 or 24 periods, with the seasonal pattern and the typical range. A single month-on-month figure cannot say whether a 3 percent fall is unusual, but a chart of 24 months can. For decisions with money behind them, a comparison of two periods is the start of the analysis: you also need to know what changed in the business between them and whether a different cause, a price change, an outage or a campaign, explains the difference.

Use the same discipline in every table you publish: the definition of revenue, the period bounds written out, the number of days and the base. The window functions are documented in DuckDB's window function reference, and the date functions in its date function reference.