SQL week over week alert: windows and thresholds

Most setups that alert when a metric drops fail the same way. Say someone picks “revenue below $10,000”, it fires every Sunday for a month, and the channel gets muted. The delivery part is easy. The hard part is deciding what “dropped” means in SQL.

This post is only about that logic. For the general pattern, see the scheduled SQL checks page. If you need the plumbing to get a result into Slack, that’s a different post. If you’re watching row counts rather than business metrics, see the same-weekday baseline for row counts. If the alert already fired and you need to know why, start with the root-cause routine.

Make the query return one number

An alert needs one value to compare. Put the whole comparison in SQL and return the percent change as a single column. Then the alert condition is a trivial pct_change < -20, and the logic lives in a query you can read and test. You can edit it whenever the definition of “normal” changes.

It also keeps the notification honest: the row carries both periods, so whoever gets paged sees this_week and last_week next to the percentage.

Week over week

The default comparison for revenue or signups is the last seven complete days against the seven before them. A window that includes a partial day will always look like a drop at 9am, so don’t use “this week so far”.

SELECT
  coalesce(sum(amount) FILTER (WHERE created_at >= current_date - 7), 0) AS this_week,
  coalesce(sum(amount) FILTER (WHERE created_at <  current_date - 7), 0) AS last_week,
  round(100.0 * (
      coalesce(sum(amount) FILTER (WHERE created_at >= current_date - 7), 0)
    - coalesce(sum(amount) FILTER (WHERE created_at <  current_date - 7), 0))
    / nullif(sum(amount) FILTER (WHERE created_at < current_date - 7), 0), 1) AS pct_change
FROM orders
WHERE created_at >= current_date - 14
  AND created_at <  current_date;

Three details matter here. The created_at < current_date bound drops today’s partial data from both windows. The FILTER clauses split one scan into two periods. And nullif(..., 0) turns a zero baseline into NULL instead of a division error.

A rolling seven days also cancels the weekday effect. If your orders dip on Sundays, a Sunday is in both windows, so it washes out. That’s why I prefer it to comparing yesterday with the day before.

Week over week still has a blind spot: it compares a week to another week that may itself have been odd. A big promo week followed by a normal one reads as a drop, even though nothing broke.

Same period last year

If your business has a season, compare against the same dates a year ago instead. Say you sell mostly in November. December will always look worse than November, so that’s an expected drop. December against last December is the fair test.

SELECT
  coalesce(sum(amount) FILTER (
    WHERE created_at >= current_date - 28 AND created_at < current_date), 0) AS this_period,
  coalesce(sum(amount) FILTER (
    WHERE created_at >= current_date - interval '1 year' - interval '28 days'
      AND created_at <  current_date - interval '1 year'), 0) AS last_year,
  round(100.0 * (
      coalesce(sum(amount) FILTER (
        WHERE created_at >= current_date - 28 AND created_at < current_date), 0)
    - coalesce(sum(amount) FILTER (
        WHERE created_at >= current_date - interval '1 year' - interval '28 days'
          AND created_at <  current_date - interval '1 year'), 0))
    / nullif(sum(amount) FILTER (
        WHERE created_at >= current_date - interval '1 year' - interval '28 days'
          AND created_at <  current_date - interval '1 year'), 0), 1) AS pct_change
FROM orders
WHERE created_at >= current_date - interval '1 year' - interval '28 days';

I use 28 days because it’s a whole number of weeks, so the weekday mix is identical in both windows. The cost is that you need more than a year of history. Fast growth also makes it a late alarm. After 80% growth, a -10% rule only fires once revenue falls to about half of today’s level (today is 1.8x last year, and 0.9x last year is half of that). So I run the year-over-year check as a second, looser alert (say below -10%) next to the weekly one.

Percent or absolute threshold

Percent thresholds are right when the metric is big enough that a 20% move is always a real event. Absolute thresholds are right when the metric is small and the business cares about a fixed amount.

Examples with made-up numbers: a store doing about $40,000 a week should alert at -20%, because $8,000 is a real hole. A shop doing about $900 a week should not alert at -20%, because $180 is one order. Its owner might care about “less than $400 this week”, and that’s an absolute threshold.

You can get both in one query by returning both columns and alerting on a combined condition:

SELECT this_week, last_week, pct_change,
       (pct_change < -20 AND last_week - this_week > 500)::int AS drop_flag
FROM (
  SELECT
    coalesce(sum(amount) FILTER (WHERE created_at >= current_date - 7), 0) AS this_week,
    coalesce(sum(amount) FILTER (WHERE created_at <  current_date - 7), 0) AS last_week,
    round(100.0 * (
        coalesce(sum(amount) FILTER (WHERE created_at >= current_date - 7), 0)
      - coalesce(sum(amount) FILTER (WHERE created_at <  current_date - 7), 0))
      / nullif(sum(amount) FILTER (WHERE created_at < current_date - 7), 0), 1) AS pct_change
  FROM orders
  WHERE created_at >= current_date - 14 AND created_at < current_date
) w;

Now the alert is drop_flag == 1. It needs a 20% drop and at least $500 lost, so a tiny baseline can’t trip it on its own. Pick the $500 from what the loss means to you.

Small numbers are noisy

Counts like signups or paid conversions have a different problem. If you usually get 12 signups a week, getting 8 is a 33% drop and almost certainly nothing. Counts fluctuate roughly by the square root of their size, so the smaller the count, the wider the normal range.

A simple guard: only judge a count when the baseline is large enough, and only flag a drop bigger than three times the square root of the baseline.

WITH w AS (
  SELECT
    count(*) FILTER (WHERE created_at >= current_date - 7) AS cur,
    count(*) FILTER (WHERE created_at <  current_date - 7) AS prev
  FROM signups
  WHERE created_at >= current_date - 14 AND created_at < current_date
)
SELECT cur, prev,
       round(100.0 * (cur - prev) / nullif(prev, 0), 1) AS pct_change,
       (prev >= 30 AND cur < prev - 3 * sqrt(prev))::int AS drop_flag
FROM w;

What that does in numbers: with a baseline of 40 signups, 3 * sqrt(40) is about 19, so you need to fall below 21, a drop of nearly half. With a baseline of 400, the cutoff is 340, a 15% drop. The rule is strict where the data is noisy and gets more sensitive as volume grows. And under a baseline of 30 it stays quiet, on purpose.

This is a rule of thumb. Backtest it on the last 90 days of your own data and count how often it would have fired. If it fires on weeks you’d call normal, raise the 3.

Wiring it up

In arcodash this is a saved query plus a monitor. A monitor reads the first row of the query’s published result, takes one named column, and compares it with a value using <, >, == and the other operators. Point it at pct_change with < -20, or at drop_flag with == 1. You can set a rearm period to throttle repeat notifications, and a monitor that gets a NULL (an empty baseline) stays in the unknown state instead of firing. The details are in the alerts overview.

There’s also a threshold-free mode that learns from the column’s last 30 observations and flags outliers. I’d still use the explicit window for revenue, because the docs are clear that it has no seasonality model, and a weekly pattern can give false positives. The comparison queries above encode your own idea of what normal means.

You can run all of this from cron too. If you want the hosted version, you can start free: 14 days, no card.