Customer order history

Description

This view provides a complete history of orders made by customers, including order details and the products included in each order.

The view is intended for:

• displaying customer order history
• tracking previous purchases
• customer support
• analyzing customer purchasing behavior

Tables covered by the view:

• Product
• Client
• Order
• Product
• makes_order
• includes

SQL код

CREATE OR REPLACE VIEW vw_customer_order_history AS
SELECT
    c.client_id,
    c.first_name,
    c.last_name,
    c.email,

    o.order_num,
    o.quantity,
    o.status,
    o.last_modified_date,
    o.payment_method,
    o.discount,

    p.code AS product_code,
    p.description AS product_description,
    p.price

FROM client c
JOIN makes_order mo
    ON mo.client_id = c.client_id
JOIN "order" o
    ON o.order_num = mo.order_num
JOIN includes i
    ON i.order_num = o.order_num
JOIN product p
    ON p.code = i.code;

Logic explanation

1. The client table provides customer information.

2. makes_order connects customers with their orders.

3. The order table provides order information.

4. includes connects each order with its products.

5. product` provides product information.

6. An order containing several products produces one row for each product.

Reason for view

This view is useful because:

• It provides a centralized customer purchase history
• It makes previous orders easy to retrieve
• It combines customer, order, and product information
• It simplifies customer support queries
• It can be used for purchasing behavior analysis

Without this view, every customer order-history query would have to repeat the joins between client, makes_order, order, includes, and product.