Remove consecutively-repeating values that occur within a date-based timeline

Question

I have a table that contains user actions based on dates. The table is used as a timeline of events. The following example shows how two people changed their job roles through time:

DECLARE @tbl TABLE (
    UserID int,
    ActionID int,
    ActionDesc nvarchar(50),
    ActionDate datetime
);
INSERT INTO @tbl (UserID, ActionID, ActionDesc, ActionDate)
VALUES 
    -- First person
    (1, 200, 'Promoted',   '2000-01-01'),   
    (1, 200, 'Promoted',   '2001-01-01'),   
    (1, 200, 'Promoted',   '2002-02-01'),   
    (1, 300, 'Moved',      '2004-03-01'),   
    (1, 200, 'Promoted',   '2005-03-01'),   
    (1, 200, 'Promoted',   '2006-03-01'),
    -- Second person
    (2, 200, 'Promoted',   '2006-01-01'),   
    (2, 300, 'Moved',      '2007-01-01'),
    (2, 200, 'Promoted',   '2008-01-01');

SELECT * FROM @tbl ORDER BY UserID, ActionDate DESC;

This gives the following, shown as the most recent event first:

I need to show the table in reverse date order, but remove any events that occur directly after they have already just occured, based on a [UserID/ActionID] match. For example, if the person was promoted, and then promoted again straight after this, the second promotion would not be included in the results, because it would be considered a duplicate of the previous action.

The desired output therefore, is:

Following research, I tried to get ROW_NUMBER() to identify the duplicates:

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY UserID, ActionID ORDER BY ActionDate ASC) AS RowNum
FROM
    @tbl
ORDER BY
    UserID, ActionDate DESC;

...But it doesn't quite work, as the numbering is not reset after each different action. I might be over-thinking this, but am struggling for inspiration because search results are returning the myriad of questions where people are simply removing duplicates from lists.

user_1595565 · Answer

I would use LEAD to eliminate rows that are unnecessary.

USE tempdb;

DECLARE @tbl TABLE (
    UserID int,
    ActionID int,
    ActionDesc nvarchar(50),
    ActionDate datetime
);
INSERT INTO @tbl (UserID, ActionID, ActionDesc, ActionDate)
VALUES 
    -- First person
    (1, 200, 'Promoted',   '2000-01-01'),   
    (1, 200, 'Promoted',   '2001-01-01'),   
    (1, 200, 'Promoted',   '2002-02-01'),   
    (1, 300, 'Moved',      '2004-03-01'),   
    (1, 200, 'Promoted',   '2005-03-01'),   
    (1, 200, 'Promoted',   '2006-03-01'),
    -- Second person
    (2, 200, 'Promoted',   '2006-01-01'),   
    (2, 300, 'Moved',      '2007-01-01'),
    (2, 200, 'Promoted',   '2008-01-01');

;WITH src AS
(
    SELECT *
        , l = LEAD(t.ActionID) OVER (PARTITION BY t.UserID ORDER BY t.ActionDate DESC)
    FROM @tbl t
)
SELECT src.UserID
    , src.ActionID
    , src.ActionDesc
    , src.ActionDate
FROM src
WHERE src.l <> src.ActionID 
    OR src.l IS NULL

The WHERE clause in the above query eliminates duplicate rows from the output where the previous row is a duplicate ActionID of the current row. The src.l IS NULL ensures we see rows with no duplicate ActionIDs.

The results:

╔════════╦══════════╦════════════╦═════════════════════════╗
║ UserID ║ ActionID ║ ActionDesc ║       ActionDate        ║
╠════════╬══════════╬════════════╬═════════════════════════╣
║      1 ║      200 ║ Promoted   ║ 2005-03-01 00:00:00.000 ║
║      1 ║      300 ║ Moved      ║ 2004-03-01 00:00:00.000 ║
║      1 ║      200 ║ Promoted   ║ 2000-01-01 00:00:00.000 ║
║      2 ║      200 ║ Promoted   ║ 2008-01-01 00:00:00.000 ║
║      2 ║      300 ║ Moved      ║ 2007-01-01 00:00:00.000 ║
║      2 ║      200 ║ Promoted   ║ 2006-01-01 00:00:00.000 ║
╚════════╩══════════╩════════════╩═════════════════════════╝

For tables with a large number of rows, you want to reduce the number of aggregates used in your query to the minimum possible; LEAD provides just this by requiring only a single aggregate. The execution plan for my version:

user_7610456 · Answer

DECLARE @tbl TABLE (
    UserID int,
    ActionID int,
    ActionDesc nvarchar(50),
    ActionDate datetime
);
INSERT INTO @tbl (UserID, ActionID, ActionDesc, ActionDate)
VALUES 
    -- First person
    (1, 200, 'Promoted',   '2000-01-01'),   
    (1, 200, 'Promoted',   '2001-01-01'),   
    (1, 200, 'Promoted',   '2002-02-01'),   
    (1, 300, 'Moved',      '2004-03-01'),   
    (1, 200, 'Promoted',   '2005-03-01'),   
    (1, 200, 'Promoted',   '2006-03-01'),
    -- Second person
    (2, 200, 'Promoted',   '2006-01-01'),   
    (2, 300, 'Moved',      '2007-01-01'), --<<--- here ActionID is 300
    (2, 200, 'Promoted',   '2008-01-01');

select UserID, ActionID, ActionDesc, min(ActionDate) as dt
  from (
         select t.*
              , row_number() over(partition by UserID, ActionID order by ActionDate)
                - row_number() over(partition by UserID order by ActionDate) as grp_id
           from @tbl t
       ) v
 group by grp_id, UserID, ActionID, ActionDesc
 order by UserID, min(ActionDate) desc;

This provides your result but only if ActionID of Moved is 300, if not, you should partition by ActionDesc instead of ActionID.

user_6409000 · Answer

SELECT * FROM
    (SELECT *, ROW_NUMBER() over (partition by Q2.userid, Q2.ActionId, rn2 order by Q2.actiondate) rn3 FROM
        (select *, Q1.rn - ROW_NUMBER() over (partition by Q1.userid, Q1.actionid order by Q1.actiondate) rn2 from 
            (SELECT *,ROW_NUMBER() over (order by userid, actiondate) rn from @tbl) Q1
        ) Q2
    ) 
Q3 Where q3.rn3 = 1 ORDER BY Q3.UserID,Q3.ActionDate

the first (inner) query assigns a row_number to every row, ordered by userid and actiondate - then I calculate a row_number same as that, but also partitioned on 'action' - if I subtact B from A, I get a number that can only apply to one group of userIds and Actions - by making yet another row_number, partitioned on userid, actionId, and my rown_number difference and ordered by date, I can then choose row 1, the earliest date.

Remove consecutively-repeating values that occur within a date-based timeline

3 Answers