Revenue spike check against the prior 3 days

Ops wants an alert when a day's revenue spikes. Aggregate PAID order revenue per day (days with no paid orders aren't in the series). For each day, compare it with a baseline: the average daily revenue of the 3 previous revenue days in the series — the current day must not be part of its own baseline.

baseline — rounded to 2 decimals; days with fewer than 3 earlier days average whatever earlier days exist; the first day has no baseline (NULL)

is_spike — boolean TRUE when daily_revenue is at least double the baseline, otherwise FALSE (including the first day)

Sort by day.

Returndaydaily_revenuebaselineis_spike

Columns

orders5 columns

columntype

order_idVARCHAR

customer_idVARCHAR

order_tsTIMESTAMP WITH TIME ZONE

statusVARCHAR

amount_usdBIGINT

The table

12 rows · 5 columns

ordersfirst 5 of 12 rows

order_idcustomer_idorder_tsstatusamount_usd

o_001c_0012026-05-10 10:00:00paid100

o_002c_0012026-05-15 11:00:00paid200

o_003c_0012026-05-20 09:30:00paid50

o_004c_0022026-05-12 14:20:00paid150

o_005c_0022026-05-25 08:15:00paid75

If you get stuck

Show hint · 1 hint Walkthrough Reveal answer

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows