I made my own schema based on your query as I don't have any sample data but see if this works.
What i did is created crap data for 12 users and you'll notice that this query should exclude user2 and user12 i believe.
If this isn't what you are looking for, use improve question link in your question to update it with sample schema and a few example data items.
DECLARE @Order TABLE
(
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Username varchar(25) NULL,
SubCategory VARCHAR(50) NULL,
OrderId INT NULL
);
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user1','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user1','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user1','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user2','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user2','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user2','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user3','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user3','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user3','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user8','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user8','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user8','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user10','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user10','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user10','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user12','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user12','1',1)
SELECT
TOP 10
Username,
COUNT(*)
FROM @Order
GROUP BY Username, SubCategory
ORDER BY COUNT(*) DESC
SELECT
TOP 10
Username,
COUNT(*)
FROM @Order
GROUP BY Username, SubCategory, OrderId
ORDER BY COUNT(*) DESC