SO.com_query_attempt_1

SELECT att.datekey datekey, att.TimeKey timekey, att.Weekday_Name, att.Peak_Hours peak_hours, att.maxatt
FROM (
        SELECT dt.datekey, dt.timekey, dt.Weekday_Name, dt.peak_hours, Avg_T_Dur_Traffic maxatt
        FROM [cat].Analytics_AvgTimes t
        INNER JOIN cat.dim_date dt ON dt.DateKey = t.DateKey AND dt.TimeKey = t.TimeKey
     ) att
INNER JOIN cat.dim_date dt ON att.DateKey = dt.DateKey AND att.TimeKey = dt.TimeKey
INNER JOIN(
            SELECT dt.DateKey datekey, dt.TimeKey timekey, dt.Peak_Hours peak_hours, dt.Weekday_Name weekday_name,
                        MAX(avg_t_dur_traffic)/60 MaxAtt
            FROM cat.Analytics_AvgTimes att
            INNER JOIN cat.Dim_Date dt ON att.datekey = dt.DateKey AND att.TimeKey = dt.TimeKey
            GROUP BY dt.DateKey, dt.Peak_Hours, dt.Weekday_Name, dt.timekey
          ) mt ON att.DateKey = mt.datekey AND att.TimeKey = mt.timekey AND att.peak_hours = mt.peak_hours AND att.maxatt = mt.MaxAtt
                    AND att.Weekday_Name = mt.Weekday_Name
--where att.DateKey = '20170523' and att.Peak_Hours <> 'off_peak'
GROUP BY att.datekey, att.peak_hours, att.weekday_name, att.MaxAtt, att.TimeKey