SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS #sales;
DROP TABLE IF EXISTS #customers;
CREATE TABLE #customers (
	id INT identity PRIMARY KEY,
	name VARCHAR(MAX)
);
CREATE TABLE #sales(
	id INT identity(1,1) PRIMARY KEY,
	customerid int,
	date date,
	amount decimal(5,2),
	details varchar(max)
);
INSERT INTO #customers(name)
VALUES('Fred'),('Wilma'),('Betty'),('Barney');
INSERT INTO #sales(customerid, date, amount,details)
VALUES
	(1,'2022-01-01',23,'Stuff'),
	(1,'2022-01-02',24,'More Stuff'),
	(1,'2022-01-03',25,'Other Stuff'),
	(2,'2022-01-04',33,'Things'),
	(2,'2022-01-05',34,'More Things'),
	(2,'2022-01-06',35,'Other Things'),
	(3,'2022-01-07',41,'Items'),
	(3,'2022-01-08',42,'More Items'),
	(3,'2022-01-09',43,'Other Items'),
	(4,'2022-01-10',14,'Whatever'),
	(4,'2022-01-11',15,'More Whatever'),
	(4,'2022-01-12',16,'Other Whatever');
16 rows affected

SELECT
	*
FROM #customers AS c CROSS APPLY (
	SELECT *
	FROM #sales AS s
	WHERE s.customerid=c.id
	ORDER BY s.date DESC OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS sq;


id
name
id
customerid
date
amount
details


1
Fred
3
1
2022-01-03
25.00
Other Stuff

2
Wilma
6
2
2022-01-06
35.00
Other Things

3
Betty
9
3
2022-01-09
43.00
Other Items

4
Barney
12
4
2022-01-12
16.00
Other Whatever


WITH CTE(customer,last_order) AS (
	SELECT customerid, max(date)
	FROM #sales
	GROUP BY customerid
)
SELECT
	*
FROM
	#sales AS s
	JOIN cte ON s.customerid=cte.customer AND s.date=cte.last_order
	JOIN #customers AS c ON s.customerid=c.id
;


id
customerid
date
amount
details
customer
last_order
id
name


12
4
2022-01-12
16.00
Other Whatever
4
2022-01-12
4
Barney

9
3
2022-01-09
43.00
Other Items
3
2022-01-09
3
Betty

6
2
2022-01-06
35.00
Other Things
2
2022-01-06
2
Wilma

3
1
2022-01-03
25.00
Other Stuff
1
2022-01-03
1
Fred

id	name	id	customerid	date	amount	details
1	Fred	3	1	2022-01-03	25.00	Other Stuff
2	Wilma	6	2	2022-01-06	35.00	Other Things
3	Betty	9	3	2022-01-09	43.00	Other Items
4	Barney	12	4	2022-01-12	16.00	Other Whatever