Is there a more efficient way to run this query for first/max transaction?

Question

For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only access (no schema or index changes). The table has well over a million rows, so creating and updating a temp table is way too slow; cross apply seems inefficient as well. With a CTE and window function I only have to hit the table twice. But is there a better approach? Simplified example of the table and my query follow:

CREATE TABLE [dbo].[trans](
    [Tran_ID] [int] IDENTITY(1,1) NOT NULL,
    [Account_ID] [varchar](50) NULL,
    [Tran_Date] [datetime] NOT NULL,
    [Tran_Amount] [money] NOT NULL
) ON [PRIMARY];

EDIT: @Peter the table has the below clustered index. This is the only index, there are no primary or foreign keys or other constraints.

CREATE CLUSTERED INDEX [idx_trans_ID_DATE] ON [dbo].[trans]
(
    [Account_ID] ASC,
    [Tran_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


WITH HighestTran AS (
            SELECT 
                Account_ID,
                Tran_Date,
                Tran_Amount,
                ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Date ASC) AS FirstTranRow,
                ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Amount DESC) AS MaxTranRow
            FROM 
                dbo.trans
            WHERE 
                Tran_Amount > 0 
            )

SELECT 
    t1.Account_ID,
    t1.Tran_Date,
    t2.Tran_Date,
    t1.Tran_Amount,
    t2.Tran_Amount
FROM
    HighestTran t1
    INNER JOIN HighestTran t2 ON t1.Account_ID = t2.Account_ID AND t1.FirstTranRow = 1 and t2.MaxTranRow = 1
ORDER BY t1.Account_ID;

Answer 1

A slight variation on your's and Thomas's that will do it with a single index scan.

WITH FlagFirstMax AS 
    (
    SELECT
        Account_ID,
        Tran_Date,
        Tran_Amount,
        ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Date ASC) isFirst,
        ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Amount DESC, Tran_Date ASC) isMax
    FROM dbo.trans
    WHERE Tran_Amount > 0
    )
SELECT Account_ID,
    MAX(CASE WHEN isFirst = 1 THEN Tran_Date END) First_Tran_Date,
    DATEDIFF(
        day,
        MAX(CASE WHEN isFirst = 1 THEN Tran_Date END),
        MAX(CASE WHEN isMax = 1 THEN Tran_Date END)
        ) Day_Duration,
    MAX(CASE WHEN isMax = 1 THEN Tran_Amount END) Max_Tran_Amount
FROM FlagFirstMax
GROUP BY Account_ID;

Answer 2

This does it in a single table scan:

SELECT Account_ID
  , MIN(Min_Date) AS DateFirst
  , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_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

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.