Write a query to get the first and last purchase date for each customer.

Let's create Table schema and feed data into table.

Table schema and sample data:-

CREATE TABLE customers (

  id   INT PRIMARY KEY,

  name VARCHAR(50) NOT NULL

);

CREATE TABLE orders (

  order_id    INT PRIMARY KEY,

  customer_id INT NOT NULL,

  order_date  DATE NOT NULL,

  amount      DECIMAL(10,2),

  FOREIGN KEY (customer_id) REFERENCES customers(id)

);

INSERT INTO customers (id, name) VALUES

(1, 'Asha'),

(2, 'Ravi'),

(3, 'Meera'),

(4, 'Kiran');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES

(1, 1, '2026-01-10', 500),

(2, 1, '2026-03-05', 300),

(3, 1, '2026-09-20', 450),

(4, 2, '2026-02-14', 200),

(5, 2, '2026-02-14', 150),

(6, 3, '2026-06-01', 800);

Query:-

Show  customer names, and include customers who never purchased

SELECT c.id, c.name, 

MIN(o.order_date) AS first_purchase_date, 

MAX(o.order_date) AS last_purchase_date 

FROM customers c 

LEFT JOIN orders o ON o.customer_id = c.id 

GROUP BY c.id, c.name ORDER BY c.id;