Try this:

DROP TABLE IF EXISTS #tempVisits;

CREATE TABLE #tempVisits (
    Visits INT,
    Day VARCHAR(10),
    Hour VARCHAR(5)
);

INSERT INTO #tempVisits (Visits, Day, Hour)
VALUES
    (83, 'Thursday', '12AM'),
    (75, 'Friday', '1AM'),
    (124, 'Tuesday', '2AM'),
    (0, 'Sunday', '3AM'),
    (0, 'Thursday', '4AM'),
    (26, 'Friday', '5AM'),
    (0, 'Monday', '6AM'),
    (7, 'Friday', '7AM'),
    (55, 'Friday', '8AM'),
    (21, 'Wednesday', '9AM'),
    (154, 'Friday', '10AM'),
    (425, 'Thursday', '11AM'),
    (270, 'Tuesday', '12PM'),
    (373, 'Friday', '1PM'),
    (0, 'Friday', '2PM'),
    (0, 'Tuesday', '3PM'),
    (0, 'Friday', '4PM'),
    (1774, 'Tuesday', '5PM'),
    (509, 'Friday', '6PM'),
     (519 , 'Saturday', '7PM');

SELECT *
FROM
(
    SELECT  *
          ,ROW_NUMBER() OVER (PARTITION BY [Day] ORDER BY [Visits] DESC) x
    FROM #tempVisits
) DS
WHERE X <= 3
ORDER BY [Day], [Visits] DESC, [Hour] -- or [x]

To show only the top three hours of each weekday, you can modify the query as follows

Query:

SELECT [DAY], Hour, VisitsAvg
FROM (
    SELECT Day, Hour, AVG(Visits) as VisitsAvg,
           ROW_NUMBER() OVER (PARTITION BY Day ORDER BY AVG(Visits) DESC) as RowNumber
    FROM #tempVisits 
    GROUP BY Day, Hour
) as t
WHERE t.RowNumber <= 3
ORDER BY Day, VisitsAvg DESC