SQL Server 2019

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

CREATE TABLE Customers(
   customer_id INTEGER  NOT NULL  
  ,name        VARCHAR(5) NOT NULL
);
INSERT INTO Customers(customer_id,name) VALUES (1,'bob');
INSERT INTO Customers(customer_id,name) VALUES (2,'alice');
INSERT INTO Customers(customer_id,name) VALUES (3,'tim');
INSERT INTO Customers(customer_id,name) VALUES (4,'zoe');

CREATE TABLE purchases(
   id          INTEGER  NOT NULL  
  ,customer_id INTEGER  NOT NULL
  ,item_bought VARCHAR(7) NOT NULL
);
INSERT INTO purchases(id,customer_id,item_bought) VALUES (1,1,'hat');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (2,1,'shoes');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (3,2,'glasses');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (3,2,'glasses');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (4,2,'book');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (5,3,'shoes');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (6,1,'hat');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (7,4,'book');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (8,4,'book');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (9,4,'pencil');
INSERT INTO purchases(id,customer_id,item_bought) VALUES (10,4,'pencil');

15 rows affected

customer_name	item_bought_most_often
alice	glasses
bob	hat
tim	shoes
zoe	pencil
zoe	book