sql - Return rows only if they are different that previous days value

Question

sales
-id
-user_id
-amount
-datetime

the sales table gets a new record for each user_id daily.

i want a select query that returns me all rows for the current day, only if the 'amount' is different that the previous days.

Update

5   123    700  2017/01/05
4   123    500  2017/01/04
3   123   1500  2017/01/03
2   123   1500  2017/01/02
1   123    500  2017/01/01

So if you search for records on the 5th, you will get 1 row since it is different that the previous days.

5   123    700  2017/01/05

But if you were run the query on the 3rd, since the amound $1500 is the same as on the 2nd, you will get 0 results back.

Hope this clears it up.

Answer 1

You could try using the Lead() and Lag() SQL Analytic function for this

SELECT *
from (SELECT *, LAG(AMOUNT) over (partition by user_id order by datetime) as Yesterday_AMOUNT
      from SALES s
     ) x
where cast(s.datetime as date) = cast(getdaate() as date) and 
       x.AMOUNT <> s.Yesterday_AMOUNT;