This does it in a single table scan:
SELECT Account_ID
, Min_Date
, MAX(DATEDIFF(day, Min_Date, Tran_Date)) AS Days_Passed
, MAX(Tran_Amount) AS Max_Amount
FROM (
SELECT Account_ID
, Tran_Date
, Tran_Amount
, MIN(Tran_Date) OVER ( PARTITION BY Account_ID) AS Min_Date
, MAX(Tran_Amount) OVER (PARTITION BY Account_ID) AS Max_Amount
FROM trans
GROUP BY Account_ID, Tran_Date, Tran_Amount
) I
WHERE I.Tran_Amount = I.Max_Amount
OR I.Tran_Date = I.Min_Date
GROUP BY Account_ID, Min_Date
This query assumes that only a few rows are found per Account_ID
. You can improve the speed of the query (which does a sort) by adding Tran_Amount
to the index. While your question does say you cannot edit the schema, at least you can have a discussion with the DBA if you see an improvement on experimental data.