left outer join - sort operations in the query plan - any ways of tuning this simple query?

while working on the query below in order to answer this question: How to query chart data in a database agnostic way? Having the following tables: CREATE TABLE [dbo].[#foo] ( [creation] DATETIME NOT NULL, [value] MONEY NULL, [DT] AS (CONVERT([date],[CREATION])) PERSISTED) -- add a clustered index on the dt column CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT) GO and this other table for joining: create table #bar (dt date primary key clustered) go the loading of data into these tables can be found here . But when running the following query: WITH RADHE AS ( SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT), THE_DATE=B.dt, THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END , THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0) FROM #BAR B LEFT OUTER JOIN #FOO F ON B.dt = F.dt ) --get rid of the duplicates and present the result SELECT THE_DATE, THE_NUMBER_OF_RECORDS_ON_THIS_DAY, THE_TOTAL_VALUE_FOR_THE_DAY FROM RADHE WHERE THE_ROW = 1 I get something like this picture below, which is exactly what I was looking for. But the execution plan generated has several Sort and Nested Loops Operations, as you can see on the picture below. The full query plan can be found here. this is a very simple operation, a left outer join between 2 tables, the indexes are already ordered, and therefore I was wondering if I could simplify the query plan. alternatively, I could change the query code. why exactly do we need nested loops 2 times and sort 2 times in the query plan?