sql challenge - customers and payments

sql_challenge_customers_payments.sql

--Challenge: Get the last payment of customer with status = 'CO'

--DDL
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(2) NOT NULL,
  `value` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_FK` (`customer_id`),
  CONSTRAINT `customer_FK` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
);

--TEST DATA
INSERT INTO customers.customer (id, name) VALUES(1, 'Leonardo'); 
INSERT INTO customers.customer (id, name) VALUES(2, 'Luiz'); 
INSERT INTO customers.customer (id, name) VALUES(3, 'Maria');

INSERT INTO customers.payment (id, status, value, customer_id) VALUES(1, 'CE', 100, 1);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(2, 'CO', 250, 1);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(3, 'CO', 350, 1);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(4, 'SO', 250, 1);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(5, 'CE', 90, 2);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(6, 'CO', 220, 2);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(7, 'CO', 320, 2);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(8, 'SO', 270, 2);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(9, 'CE', 120, 3);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(10, 'CO', 240, 3);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(11, 'CO', 340, 3);
INSERT INTO customers.payment (id, status, value, customer_id) VALUES(12, 'SO', 260, 3);




--SOLUTION
SELECT  
	p.id,
	c.name
FROM customer c
JOIN (
	SELECT MAX(id) last_id, customer_id 
	FROM payment
	WHERE status = 'CO'
	GROUP BY customer_id
) last_payment ON (c.id = last_payment.customer_id)
JOIN payment p ON p.id = last_payment.last_id;