Accessing Previous Rows (LAG)

Problem

The sales team wants to analyze customer purchasing behavior over time. Specifically, they want to compare each order's amount against the amount of that customer's previous order. Retrieve customer_id, order_date, amount, and the previous order amount as prev_amount for each customer (ordered by date).

Database Schema

Table: customers

• `customer_id` (INT): Unique identifier for the customer.
• `first_name` (VARCHAR): First name.
• `last_name` (VARCHAR): Last name.

Table: products

• `product_id` (INT): Unique identifier for the product.
• `name` (VARCHAR): Product name.
• `price` (DECIMAL): Product price.
• `category` (VARCHAR): Product category.

Table: orders

• `order_id` (INT): Unique identifier for the order.
• `customer_id` (INT): Foreign key referencing the customer.
• `order_date` (DATE): Date of the order.
• `amount` (DECIMAL): Total order amount.

Try It

Hint

Solution