Derive or modify a few columns using an existing column in SQL database

I want to add three columns LDate, TCount and LApprover by using Date, Count, id columns which are present in the SQL database table.

The process involves specific SQL queries to extract relevant information for each of the new columns.

The initial step is to calculate the total count, denoted as TCount, by executing the following SQL query

SELECT SUM(Count) AS TCount 
FROM db 
GROUP BY id; 

I want to determine the maximum date, labeled as LDate using the following SQL query:

SELECT MAX(Date) AS LDate 
FROM db 
GROUP BY id;

The logic for deriving the LApprover column involves selecting the maximum approver based on conditions related to the LDate and Date columns. The SQL query is as follows:

SELECT
    MAX(CASE 
            WHEN [LDate] = [Date] OR [LDate] IS NULL 
                THEN [Approver] 
                ELSE NULL 
        END) AS [LApprover] 
FROM db 
GROUP BY id; 

Current table:

Expected result:

I have tried this query, but I am not getting the expected result:

WITH CombinedCTE AS 
(
    SELECT 
        q1.id, q2.Count, 
        q1.[TCount], q2.Date, q1.LDate, q2.Approver 
    FROM 
        (SELECT 
             id, COUNT(Count) AS [TCount], MAX([Date] AS LDate 
         FROM 
             db 
         GROUP BY 
             id) q1    
    JOIN 
        (SELECT id, Count, [Date], Approver 
         FROM db) q2  ON q1.id = q2.id 
    WHERE 
        q2.id = 'E3' 
)
SELECT 
    id, Approver, Count, TCount, Date, LDate,
    MAX(CASE WHEN [LDate] IS NULL OR [LDate] = [Date] THEN [Approver] ELSE NULL END) AS [LApprover] 
FROM 
    (SELECT * FROM CombinedCTE) SubQuery
GROUP BY 
    id, Approver, Count, TCount, Date, LDate 

The result that I am getting is this:

There is no need for such complex joining. You can just use window functions for this.

SELECT *,
  SUM(Count) OVER (PARTITION BY Id) AS TCount,
  MAX(Date) OVER (PARTITION BY Id) AS LDate,
  FIRST_VALUE(Approver) OVER
    (PARTITION BY Id ORDER BY Date DESC ROWS UNBOUNDED PRECEDING) AS LApprover
FROM db;

db<>fiddle