Torque Stats with a CTE

Using a Common Table Expression in SQL to analyze some Performance stats from the OBD scanner.

;WITH cteRawData AS (
SELECT CAST(TripAvgMPG as float) TripAvgMPG,
runid,
ROW_NUMBER() OVER(PARTITION by runid order by CAST(DeviceTime as datetime)  DESC) as RowNum
from rawdata
)
SELECT c.TripAvgMPG,
c.RowNum,
c.runid, 
AVG(CAST(r.SpeedOBD as float)) AvgSpeedOBD, 
MAX(CAST(r.SpeedOBD as float)) MAXSpeedOBD,
MAX(CAST(r.DeviceTime as DateTime)) DeviceTime, 
AVG(CAST(r.AbsThrottlePositionPCT as float)) AVGAbsThrottlePositionPCT, 
AVG(CAST(r.EngineRPM as Float)) AvgEngineRPM,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeMoving as float)) - MIN(CAST(TripTimeMoving as float)),0),108) TripTimeMoving, 
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeOverall as float)) - MIN(CAST(TripTimeOverall as float)),0),108) TripTimeOverall,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeStationary as float)) - MIN(CAST(TripTimeStationary as float)),0),108) TripTimeStationary
FROM cteRawData c 
JOIN rawdata r ON c.runid = r.runid
WHERE c.RowNum = 1
GROUP BY c.TripAvgMPG,c.RowNum,c.runid
order by c.TripAvgMPG