Detecting Seasonality in a CSV of Daily Sales Data

Daily sales have rhythm. Restaurants peak on Friday and Saturday, B2B software sells on weekdays, tax software on April afternoons. If you compare Tuesday with Saturday without knowing that, you will report a collapse that is a calendar. Finding the rhythm is simple work in SQL, and it matters for two reasons: it tells you what is normal for a given day, so that real changes stand out, and it stops you from comparing periods that do not contain the same mix of days. This article finds a weekly pattern in a CSV of daily sales, tests for it with autocorrelation and shows what the output looks like when there is no pattern at all. It is one of the moves in more analysis moves for CSV data, and a follow-on from the date handling in resampling a CSV time series to a day.

One row per day first

Seasonality lives in time series, and a CSV of orders is not one until it is aggregated. Collapse to a single number per day:

CREATE TABLE s AS
SELECT created_at::DATE AS day, sum(amount) AS sales
FROM read_csv('orders.csv') GROUP BY 1 ORDER BY 1;

Check the result has no gaps: a day with no orders does not appear in a GROUP BY, so a shop that was closed on a holiday has a missing row and not a zero. Gaps break lags (a seven-row lag is no longer seven days) and bias the average. Fill them with a calendar join, as in filling gaps in a CSV time series, and decide whether a missing day is a zero or unknown. Also decide on the measure: revenue, order count and average order value have different rhythms, and the weekly pattern of orders can be the opposite of the pattern of basket size.

The weekday index

The simplest view of a weekly pattern is the average for each weekday relative to the overall average. Divide each by the overall mean and multiply by 100, so 100 is average and 70 means 30 percent below:

WITH w AS (SELECT dayofweek(day) AS dow, avg(sales) AS a FROM s GROUP BY 1)
SELECT strftime(date '2025-01-05' + dow::INTEGER, '%a') AS weekday,
       round(a) AS avg_sales, round(100 * a / avg(a) OVER (), 1) AS index
FROM w ORDER BY dow;

In DuckDB, dayofweek returns 0 for Sunday through 6 for Saturday, and the date arithmetic turns the number back into a name (5 January 2025 was a Sunday). On the synthetic series, which I built with weekend dips and a Friday peak, the output was:

weekday  avg_sales  index
Sun        7231.0    69.9
Mon       10506.0   101.6
Tue       11074.0   107.1
Wed       11397.0   110.2
Thu       11610.0   112.3
Fri       12110.0   117.1
Sat        8472.0    81.9

The pattern is plain: Sunday is 30 percent below average and Friday 17 percent above. The built-in multipliers were 0.70 for Sunday, 0.80 for Saturday and 1.15 for Friday, and the index recovers them, with the drift from trend and noise accounting for the small differences. This table is also a ready-made correction: dividing each day's sales by its weekday index removes the weekly cycle and leaves a series in which a real change is easier to see.

Run the same query on the 2 million row file, which was generated with no weekly rhythm, and the result is flat:

weekday  avg_revenue  index
Sun         439884.0  100.2
Mon         437342.0   99.7
Tue         438580.0   99.9
Wed         439715.0  100.2
Thu         438594.0   99.9
Fri         439441.0  100.1
Sat         438271.0   99.9

Every day is within 0.3 percent of average. That is what a file with no weekly cycle looks like, and it is worth keeping in your head as the comparison, since a real business with an index between 99.7 and 100.2 is unusual enough to suspect that the dates are not what you think they are (a column of load dates and not order dates, for example).

Autocorrelation at lag 7

An index table works if you already suspect a weekly cycle. A test that does not need a guess is the autocorrelation: the correlation between the series and itself shifted by some number of days. A series with a weekly cycle correlates strongly with itself shifted by 7, 14 and 21. In SQL, lag does the shift:

WITH x AS (SELECT sales, lag(sales, 7) OVER (ORDER BY day) AS prev FROM s)
SELECT corr(sales, prev) FROM x;

The aggregate has to sit outside the window function, which is why this is a subquery. Running it for several lags on the synthetic series:

lag  1 autocorrelation: 0.33
lag  7 autocorrelation: 0.93
lag 14 autocorrelation: 0.92
lag 30 autocorrelation: -0.31

Lag 7 and 14 are around 0.9, so a day resembles the same day a week and two weeks before. Lag 1 is modest, since adjacent days are in different phases of the cycle. Lag 30 is negative because 30 days is not a multiple of 7: it is four weeks and two days, so it pairs days that are two apart in the cycle. This shape, high at multiples of 7 and low between them, is the fingerprint of weekly seasonality. On the flat 2 million row file, lag 7 gave -0.02, which is nothing. A value near zero at the candidate lag is as informative as a high one.

For other cycles, change the lag. Month-end effects need daily data over many months and show as a spike at a lag near 30 or 31, with the caution that months have different lengths; for annual patterns, aggregate to months and look at lag 12 of the monthly series, which needs several years. Calculating a moving average over a CSV is the next step, since a seven-day average is the simplest way to remove a weekly cycle and see the trend underneath.

Using what you found

Two uses justify the work. The first is comparison. A fair comparison of two periods has the same number of each weekday, or compares per-weekday averages, or uses the seven-day moving average. A week with an extra Friday looks better than a week with an extra Sunday for no reason that matters (comparing two time periods in a CSV). The second is anomaly detection: once you know Sunday is normally 70 percent of average, a Sunday at 90 percent is a good day and a Wednesday at 90 percent is a bad one, and the same revenue number means opposite things. Compare each day with its weekday's expectation, and alert on the residual (detecting sudden drops in a CSV metric).

How much data you need

A pattern claim needs repetition. A weekly rhythm seen in two weeks of data could be a coincidence of one unusual week, and the index for each weekday would be an average of two numbers. I would want eight to twelve weeks before believing an index, and I would look at the spread within each weekday and not only the mean. For yearly patterns, two full years is the minimum, and the trend has to be removed first or it will masquerade as a season. Holidays, promotions and outages are not seasonality, and they distort the index: drop or flag the unusual days before averaging, or use the median.

When the series is too short for seasonality

If the file covers three weeks, there is no seasonality to find, only a description of those three weeks. If the volume is a few orders a day, daily totals are noise, and the weekly pattern is better seen by aggregating to weeks or by counting orders by weekday over a long period. If the business changes during the period, a new channel in March, a price change in June, the pattern in the first half need not hold in the second, and an average over the whole file is a blend that describes neither.

Do not forecast from an index alone, either: it describes a repeating shape, and a forecast needs a trend and an uncertainty too, which is a modeling job (Forecasting: Principles and Practice is a good free reference). The functions used above are documented in DuckDB's window functions and date functions pages.