Calculating interval in days based on average velocity

I have an `orders` table and need to calculate when there is a change in the average count across time. This could be either an increase or decrease.

For instance, if there is usually 1 order every two days, I need to detect if that suddenly increased to 3 orders every day or decreased to 1 order every 3 days.

Example table structure and data:

``````CREATE TABLE "public"."orders" (
"id" SERIAL,
"created_at" timestamp NOT NULL,
PRIMARY KEY ("id")
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');
``````

In the data set above, supposed I wanted to detect that over the course of the last week there is usually only 1 order every 2 days, but over the last 24 hours that pattern has been violated with 3 orders in one day.

Desired result:

• Previous 7 day's average orders per day (not including last 24 hours): 0.43
• Previous 7 day's average order velocity (not including last 24 hours): 1 order every 2 days
• Last 24 hour's orders: 3

Alternate scenario:

``````INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '7 days'),
(now() - interval '7 days'),
(now() - interval '7 days');
``````

Alternatively, suppose I have the above data set. Normally there are approximately 3 orders every day for the past week, but over the last 24 hours there was only 1 order.

Desired result:

• Previous 7 day's average orders (not including last 24 hours): 2.57
• Previous 7 day's average order velocity (not including last 24 hours): 3 orders every 1 days
• Last 24 hour's orders: 1

How would I do this?

PostgreSQL 9.6

• Please add the desired result. May 10, 2017 at 14:09
• @McNets I've updated the question to be more descriptive and include data for each scenario. May 10, 2017 at 20:50
• Yes, but could you add a desired result? how many days should be considered? every 2 days? by week? May 10, 2017 at 20:53
• To calculate a tendency I need a period of time between samples. May 10, 2017 at 21:00
• @McNets question updated. Let me know if this is what you need. May 10, 2017 at 21:18

First count orders grouped by day of last 7 days, and then calculate averages

``````--= Count orders grouping by date
WITH cn AS
(
SELECT created_at::date, COUNT(*) num_orders
FROM   orders
WHERE  created_at <  (now() - interval '1 days')
AND    created_at >= (now() - interval '8 days')
GROUP BY created_at::date
)
SELECT to_char(AVG(num_orders), '9.99') AS "AvgOrderVelocity",
to_char(SUM(num_orders) / 7::decimal(18,2), '0.99')  AS "AvgLast7Days",
(SELECT COUNT(*)
FROM   orders
WHERE  created_at > (now() - interval '1 days')) "Last24h"
FROM   cn;
``````

Using first sample data:

```Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-05 |          1
2017-05-07 |          1
2017-05-09 |          1
```
``````AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
1.00            |  0.43        |       3
``````

dbfiddle here

Using second sample data:

```Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-04 |          3
2017-05-05 |          3
2017-05-06 |          3
2017-05-07 |          3
2017-05-08 |          3
2017-05-09 |          3
```
``````AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
3.00            |  2.57        |       1
``````

dbfiddle here

• When I run this on my database, I get an accurate number for `Last24h` but I get `#.##` in both the `AvgOrderVelocity` and `AvgLast7Days`. Any idea why that is? May 16, 2017 at 0:11
• Ahh, I see the problem. The numbers I get on my dataset are so large that `'9.99'` in the char cast is insufficient. Changing to `'999.99'` solved the issue. However, I have no idea just how many digits a given result will have. Is there any reason not to just do `round(AVG(num_orders), 2)`? May 16, 2017 at 0:45
• No there isn't, I've just used this format to fit your desired result. May 16, 2017 at 7:40