SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Storetable 
    (personID int,[storename] varchar(5))
;
    
INSERT INTO Storetable 
    (personID,[storename])
VALUES
    (1,'ASDA'),
    (2,'ASDA'),
    (3,'Tesco'),
    (1,'Tesco'),
    (4,'ASDA'),
    (5,'M&S'),
    (6,'M&S'),
    (4,'Tesco')
;
8 rows affected

CREATE TABLE Customertable 
    ([personID] int, [lastname] varchar(6), [memberpoints] int)
;
    
INSERT INTO Customertable 
    ([personID], [lastname], [memberpoints])
VALUES
    (1, 'Smith', 8000),
    (2, 'Henry', 6500),
    (3, 'Johns', 6450),
    (4, 'Gregs', 6220),
    (5, 'Jacobs', 6000),
    (6, 'Abdul', 5550)
;
6 rows affected

WITH CTE AS 
(SELECT St.storename, Cu.lastname, SUM(Cu.memberpoints) AS [memberpoints]
, ROW_NUMBER() OVER(PARTITION BY St.storename ORDER BY SUM(Cu.memberpoints) DESC) rn
FROM
Storetable AS St
JOIN
Customertable AS Cu
ON
St.personID = Cu.personID
GROUP BY St.storename,Cu.lastname)
SELECT storename,lastname,memberpoints FROM CTE WHERE rn = 1



storename
lastname
memberpoints


ASDA
Smith
8000

M&S
Jacobs
6000

Tesco
Smith
8000