Full fan trap example

Setting up

To run this you need to access a Postgres database.

I opened up the psql command and created a new databse, then switched to it:

psql
> create database banana;
> \c banana

After this you can just call psql banana. You don’t have to call your database banana, by the way.

If you save the scripts here into files rather than pasting them the command \c filename.sql will let you run them.

Create the tables

DROP TABLE IF EXISTS sales_targets;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name text NOT NULL
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_date date NOT NULL
);

CREATE TABLE order_lines (
    order_line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(order_id),
    product_name text NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0)
);

CREATE TABLE sales_targets (
    sales_target_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    target_period date NOT NULL,
    target_amount numeric(12,2) NOT NULL CHECK (target_amount >= 0),
    UNIQUE (customer_id, target_period)
);

Add some data

INSERT INTO customers (customer_name)
VALUES ('Acme Industries');

INSERT INTO orders (customer_id, order_date)
VALUES (
    (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
    DATE '2026-07-01'
);

INSERT INTO order_lines (order_id, product_name, quantity, unit_price)
VALUES
    (
        (SELECT order_id FROM orders WHERE customer_id = (
            SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'
        ) AND order_date = DATE '2026-07-01'),
        'Widget A',
        2,
        19.99
    ),
    (
        (SELECT order_id FROM orders WHERE customer_id = (
            SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'
        ) AND order_date = DATE '2026-07-01'),
        'Widget B',
        1,
        49.50
    ),
    (
        (SELECT order_id FROM orders WHERE customer_id = (
            SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'
        ) AND order_date = DATE '2026-07-01'),
        'Service Plan',
        1,
        9.99
    );

INSERT INTO sales_targets (customer_id, target_period, target_amount)
VALUES (
    (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
    DATE '2026-07-01',
    250.00
);

Run the query

So if we were to naively ask for a sum of the targets and a sum of the orders we’d expect the targets to be 250. But they aren’t:

SELECT
    c.customer_name,
    SUM(ol.quantity * ol.unit_price) AS order_total,
    SUM(st.target_amount) AS sales_target_total
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id
JOIN order_lines AS ol
    ON ol.order_id = o.order_id
JOIN sales_targets AS st
    ON st.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

  customer_name  | order_total | sales_target_total 
-----------------+-------------+--------------------
 Acme Industries |       99.47 |             750.00

So, can you work out how to fix this?