MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.26


create table Employees (EmployeeID int primary key, FirstName varchar(100), LastName varchar(100));
insert into Employees values (1, 'John', 'Smith'), (2, 'Mary', 'Miller');
create table Products (ProductID int primary key, ProductName varchar(100));
insert into Products values (1, 'Product 1'),  (2, 'Product 2');
create table Orders (OrderID int primary key, EmployeeID int);
insert into Orders values (1, 1), (2, 1), (3, 2), (4, 2);
create table OrderDetails (OrderID int, ProductID int);
insert into OrderDetails values (1, 1), (1, 2), (2, 2), (3, 1), (4, 1), (4, 2);
SELECT 
  concat_ws(' ',
           e.FirstName,
           e.LastName) as "Employee name",
  count(*) as "Num of sales",
  (
    SELECT p2.ProductName 
    FROM Orders o2
    INNER JOIN OrderDetails od2 ON od2.OrderID = o2.OrderID 
    INNER JOIN Products p2 ON p2.ProductID = od2.ProductID 
    WHERE o2.EmployeeID = o.EmployeeID
    GROUP BY p2.ProductID
    ORDER BY count(*) DESC
    LIMIT 1
  ) as "Product Name"
FROM Orders o 
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID 
INNER JOIN OrderDetails od ON od.OrderID = o.OrderID 
GROUP BY o.EmployeeID 
ORDER BY count(*) desc;


Employee name
Num of sales
Product Name


John Smith
3
Product 2

Mary Miller
3
Product 1


SELECT 
  concat_ws(' ',
           e.FirstName,
           e.LastName) as "Employee name",
  count(*) as "Num of sales"
FROM Orders o 
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID 
INNER JOIN OrderDetails od ON od.OrderID = o.OrderID 
GROUP BY o.EmployeeID 
ORDER BY count(*) desc;


Employee name
Num of sales


John Smith
3

Mary Miller
3


SET sql_mode = '';
SELECT 
  concat_ws(' ',
           e.FirstName,
           e.LastName) as "Employee name",
  count(*) as "Num of sales",
  (
    SELECT p2.ProductName 
    FROM Orders o2
    INNER JOIN OrderDetails od2 ON od2.OrderID = o2.OrderID 
    INNER JOIN Products p2 ON p2.ProductID = od2.ProductID 
    WHERE o2.EmployeeID = o.EmployeeID
    GROUP BY p2.ProductID
    ORDER BY count(*) DESC
    LIMIT 1
  ) as "Product Name"
FROM Orders o 
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID 
INNER JOIN OrderDetails od ON od.OrderID = o.OrderID 
GROUP BY o.EmployeeID 
ORDER BY count(*) desc;


Employee name
Num of sales
Product Name


John Smith
3
Product 2

Mary Miller
3
Product 1