Create new columns with MAX value of one column, partitioned and filtered on other columns

Question

I am trying to generate the following results set from the raw data below:

Raw Data (SourceTable):

Desired Results set:

Essentially I want the MAX value partitioned over each UserID and Store and filtered by Product, with a column for only Apple values and a column for only Grape values. The only way I've been able to achieve this is to create separate CTE tables for each new column that partition and filter the data and then join them together but this is causing performance issues.

WITH cte AS
(
       SELECT *
       FROM   SourceTable]), cte2 AS
(
       SELECT *,
              Max([Value]) OVER (partition BY [UserID], [Store]) AS max_value_by_apples
       FROM   cte
       WHERE  [Product] = 'Apples'), cte3 AS
(
       SELECT *,
              Max([Value]) OVER (partition BY [UserID], [Store]) AS max_value_by_grapes
       FROM   cte
       WHERE  [Product] = 'Grapes')
SELECT    a.*,
          b.max_value_by_apples,
          c.max_value_by_grapes
FROM      cte a
LEFT JOIN cte2 b
ON        a.[UserID] = b.[UserID]
AND       a.[Store] = b.[Store]
LEFT JOIN cte3 c
ON        a.[UserID] = c.[UserID]
AND       a.[Store] = c.[Store]

Any help would be greatly appreciated.

Answer 1

query

SELECT *,
   Max([Value]) OVER (partition BY [UserID], [Store]) AS max_value_by_grapes
FROM   datatable
WHERE  [Product] = 'Grapes'

is like

select *
  ,max(case when product='Apples' then value end)over(partition by userid,store) max_value_by_apples
FROM datatable

OR expression max(case when product='Apples' then value end) can be used instead filter (or as filter) where product='Apples' in window function.

See example

select *
  ,max(case when product='Apples' then value end)over(partition by userid,store) max_value_by_apples
  ,max(case when product='Grapes' then value end)over(partition by userid,store) max_value_by_grapes
from data

fiddle