SQL query-writing questions

SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) FROM orders WHERE YEAR(created_at) = YEAR(CURDATE()) GROUP BY month ORDER BY month; (function names vary by database).

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use DENSE_RANK() in databases that support window functions.

First: SELECT COUNT(*) FROM orders WHERE created_at < DATE_SUB(CURDATE(), INTERVAL 1 YEAR); check the count; then run DELETE with the same WHERE inside a transaction.

SELECT c.name, SUM(o.total) AS spent FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.name ORDER BY spent DESC LIMIT 3;

Start from a statuses table (or a VALUES list) and LEFT JOIN orders, then GROUP BY status and COUNT(o.id). COUNT(*) would wrongly count the empty join as 1.