left outer join - sort operations in the query plan - any ways of tuning this simple query?
You have an index that provides ordering by B.DT but 


the plan first evaluates THE_ROW using this order 
then the right hand sort orders by F.DT to evaluate THE_NUMBER_OF_RECORDS_ON_THIS_DAY 
and finally the left hand sort puts things back into B.DT order for the THE_TOTAL_VALUE_FOR_THE_DAY.


You can get rid of one of the sorts by simply changing the order of the columns in the CTE so the F.DT one appears last (The connect item for this Unnecessary Sort is here)

WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0),
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)


But you can get rid of both by changing the definition of THE_NUMBER_OF_RECORDS_ON_THIS_DAY to

CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY B.DT ) END


So it uses the same partitioning definition as the rest of the functions.

This shouldn't change anything in your example as your CASE expression will just assign 0 to any non matched rows anyway. 

As for the rest of the plan see Partitioning and the Common Subexpression Spool

(Plan afterwards with no sorts)