Example: Calculating the Average Order Value for the Last 3 Months

In this lecture, let's check out a cool practical example.

The average order value is a metric that shows how much, on average, a customer spends per purchase. It's one of the key business metrics that lets you:

• analyze changes in purchasing power,
• spot sales trends,
• evaluate how effective your marketing campaigns are.

Task Statement

Imagine we have a database with an orders table where orders are stored. Our goal:

1. Calculate the average order value for orders made in the last three months.
2. Automate this calculation with a procedure.
3. Save the result in a separate table for further analysis.

Expanding Our Database: orders Table Structure

First, let's make sure we've got a table with the data we need. Here's what the orders table structure might look like:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

• order_id — unique order identifier.
• customer_id — the customer who placed the order.
• order_date — the date the order was placed.
• total_amount — total order amount.

For the example, let's add a few records to the table so we've got something to work with:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Manual Calculation of the Average Order Value

Before we automate the process, let's write a basic query that calculates the average order value for the last 3 months. We'll use the current date (CURRENT_DATE) and the AVG() function to get the average.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

Here's what's going on:

• AVG(total_amount) — aggregate function that calculates the average value of total_amount.
• CURRENT_DATE - INTERVAL '3 months' — picks orders made in the last three months.
• ROUND(..., 2) — rounds the result to two decimal places.

The query result will look something like this:

Automation with a Procedure

Now our task is to create a procedure that will do this calculation automatically and log the result in a separate table. First, let's create a table to store analytics logs.

Creating the log_analytics table

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — date and time of the record.
• metric_name — metric name (in our case, "averagecheck_3_months").
• metric_value — calculated metric value.

Creating the Procedure

Now let's write a procedure that:

1. Calculates the average order value for the last three months.
2. Saves the result in the log_analytics table.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Step 1: Calculate the average order value
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Step 2: Log the result
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Debug info (optional)
    RAISE NOTICE 'Average order value: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Now you can call this function and it'll automatically write the result to the log_analytics table:

SELECT calculate_average_check();

Automation with a Task Scheduler

In the last lecture, we already set up a task scheduler. If you're on Linux, that was the pg_cron extension; if you're using Windows or macOS, you probably set up a run through the system scheduler (cron or Task Scheduler). Now that everything's ready, let's hook up our procedure to a schedule.

If you're on Linux and using pg_cron, make sure the extension is enabled in the right database:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Reminder: installing pg_cron itself and setting the shared_preload_libraries parameter was already covered in the previous lesson.)

Now you can schedule our calculate_average_check() function — for example, every day at midnight:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Explanation:

• 'daily_avg_check' — task name;
• '0 0 * * *' — cron expression to run at 00:00 every day;
• the command inside $$ — the SQL that will be executed.

If you're on Windows or macOS, pg_cron doesn't work on these systems (on Windows — not at all, on macOS — needs manual build). But you've already set up a system scheduler — all that's left is to hook up the SQL file.

1. Create a file with the query:

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. Use psql to run the file on a schedule:

   • On Linux/macOS:

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (added via crontab -e)
   • On Windows Task Scheduler:
     • Set the path to psql.exe.
     • In the arguments:
       -U postgres -d your_database -f "C:\path\to\script.sql"

So, no matter what system you're on, the procedure will run automatically and regularly log the average order value in the log_analytics table. If you're not sure which way you're using, go back to the previous lecture — it covers installing and setting up the scheduler for different platforms.

Checking and Analyzing the Results

Let's see what we've got. Let's query the data from the log_analytics table:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Example result:

Now we've got a log of all average order value calculations! You can use this data to generate reports or analyze how the metric changes over time.

Common Mistakes and How to Avoid Them

Working with analytics procedures for calculating the average order value can come with a few typical mistakes.

One of them is forgetting to handle empty results. If there were no orders in the last three months, the AVG() function will return NULL, which can cause issues when logging. To avoid this, you can use COALESCE():

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Another mistake is having bad data in the orders table. For example, negative order amounts or invalid dates. It's a good idea to regularly check your data or add constraints at the database level (like CHECK (total_amount > 0)).

Congrats, now you've got a full-on procedure that automatically calculates the average order value for the last three months and saves the result for further analysis. This is just one of many examples of how PostgreSQL and PL/pgSQL can help automate analytics tasks. In the next lecture, we'll keep digging into more complex analytics scenarios. See you then!