PostgreSQL Interval Calculation – How to Calculate Interval in Days Based on Average Velocity

postgresqlpostgresql-9.6

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

Best Answer

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

Related Question