1

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;
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;
| improve this answer | |
  • Advice from you and @Thomas Kejser both reduced the query runtime by 20%. I upvoted both as great solutions. I chose this as the accepted answer because CTEs are easier for me to review in the future, after I've forgotten why I wrote the SQL in a certain way. – Nicholai Aug 28 '14 at 2:28
  • @nicholai I'm glad you got an improvement. I mocked up a test dataset and I didn't see a great deal of time difference between the three. – MickyT Aug 28 '14 at 2:38
  • @Nicholai: You might want to compare the two solutions once you add the index I suggested. I don't think MickyT's solution will benefit from an index – Thomas Kejser Aug 31 '14 at 10:56
1

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.

| improve this answer | |
  • 1
    A person can have multiple transactions, so a buying pattern like ('QD1','2011-01-01', 20), ('QD1','2012-01-01', 100), ('QD1','2013-01-01', 100) returns 3 results. It should return 1 result of ('QD1', 365, 100). I will speak with the vendor to see about the possibility of adding indexes; it's a kind of legacy export procedure that creates new tables each time. – Nicholai Aug 26 '14 at 12:19
  • A simple modification (done now) will fix that in the query above. – Thomas Kejser Aug 26 '14 at 13:41
  • Thanks Thomas; however, I still find cases using those three sample records where the wrong date is pulled (returning 731 for Days_Passed). To duplicate, try inserting the 2012 record first, then 2013, then 2011. We can't guarantee the order of evaluation of the OR operator, or the order in which it will search records. Replacing the OR with a CASE in the WHERE clause will let me set order of evaluation and hope that the earliest gift comes first, but even that doesn't guarantee the order in which records will be searched. – Nicholai Aug 26 '14 at 18:44
  • @Nicholai: I see where you are coming from. You want the interval to be the shortest one between the first transaction and the highest amount right? In other words, if there is more than one record with the highest amount, you want the smaller of the intervals? I will look at the query again in the morning and modify it to work like that. – Thomas Kejser Aug 27 '14 at 1:34
  • @Nicholai: I made the modification as it was rather quick. This returns the desired result (?) of 365 days. However, in the case where there is only one transaction for the account Ex: ('QD3', 2011-01-01, 42) it returns NULL in the interval length. If the desired result is 0 instead, just put a COALESCE in there. – Thomas Kejser Aug 27 '14 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.