CODEWITHSUNDEEP
sqlsql-server
camnesia
camnesia

Reputation: 2323

SQL - group by a change of value in a given column

Apologies for the confusing title, I was unsure how to phrase it.

Below is my dataset:

+----+-----------------------------+--------+
| Id |            Date             | Amount |
+----+-----------------------------+--------+
|  1 | 2019-02-01 12:14:08.8056282 |     10 |
|  1 | 2019-02-04 15:23:21.3258719 |     10 |
|  1 | 2019-02-06 17:29:16.9267440 |     15 |
|  1 | 2019-02-08 14:18:14.9710497 |     10 |
+----+-----------------------------+--------+

It is an example of a bank trying to collect money from a debtor, where first, 10% of the owed sum is attempted to be collected, if a card is managed to be charged 15% is attempted, if that throws an error (for example insufficient funds), 10% is attempted again.

The desired output would be:

+----+--------+---------+
| Id | Amount | Attempt |
+----+--------+---------+
|  1 |     10 |       1 |
|  1 |     15 |       2 |
|  1 |     10 |       3 |
+----+--------+---------+

I have tried:

SELECT Id, Amount
FROM table1
GROUP BY Id, Amount

I am struggling to create a new column based on when value changes in the Amount column as I assume that could be used as another grouping variable that could fix this.

Upvotes: 2

Views: 1516

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If you just want when a value changes, use lag():

select t.id, t.amount,
       row_number() over (partition by id order by date) as attempt
from (select t.*, lag(amount) over (partition by id order by date) as prev_amount
      from table1 t
     ) t
where prev_amount is null or prev_amount <> amount

Upvotes: 2

Related Questions