SQL: find records whose given field has different string values in consecutive days

Question

I have the following table and I need to find two consecutive days which have different status for the same person (No gap between days).

name    date           status
-------------------------------
John    2020-06-01     submitted
John    2020-06-02     pending
John    2020-06-03     approved
John    2020-06-04     approved
Amy     2020-06-02     pending
Amy     2020-06-03     pending
Amy     2020-06-04     pending
Dan     2020-06-02     submitted
Dan     2020-06-03     approved
Dan     2020-06-04     approved
Mary    2020-06-03     submitted
Mary    2020-06-04     pending

output should like below:

name    date           status
-------------------------------
John    2020-06-01     submitted
John    2020-06-02     pending
John    2020-06-03     approved
Dan     2020-06-02     submitted
Dan     2020-06-03     approved
Mary    2020-06-03     submitted
Mary    2020-06-04     pending

Currently I exported the table and wrote a python code to do that. However, I am wondering would it be possible to achieve that just using SQL? (I looked into SQL: retrieve only the records whose value has changed , but couldn't figure out how to make it work in my case since the status field is a string instead of number) Thanks!

Are there gaps between the dates? – forpas Jul 04 '20 at 16:52 — forpas, Jul 04 '20 at 16:52

score 0 · Answer 1 · answered Jul 04 '20 at 16:59

I'd self-join the table according to the name, consecutive days and different statuses:

SELECT a.name, a.date, a.status
FROM   mytable a
JOIN   mytable b ON a.name = b.name AND
                    a.date + INTERVAL '1' DAY = b.date AND
                    a.status <> b.status

score 0 · Answer 2 · edited Jul 04 '20 at 18:16

With LAG() and LEAD() window functions:

select t.name, t.date, t.status
from (
  select *,
    coalesce(lag(status) over (partition by name order by date), status) prev_status,
    coalesce(lead(status) over (partition by name order by date), status) next_status
  from tablename  
) t
where t.status <> t.prev_status or t.status <> t.next_status
order by t.name, t.date

See the demo.
Results:

| name | date       | status    |
| ---- | ---------- | --------- |
| Dan  | 2020-06-02 | submitted |
| Dan  | 2020-06-03 | approved  |
| John | 2020-06-01 | submitted |
| John | 2020-06-02 | pending   |
| John | 2020-06-03 | approved  |
| Mary | 2020-06-03 | submitted |
| Mary | 2020-06-04 | pending   |

SQL: find records whose given field has different string values in consecutive days

2 Answers2