SQL: is there a query that returns records only when there is an increment of 1 in a field?-CodePudding

sample_table

Hello. I am looking for a query that will only return the case where there was any increase in the 'numoflaptop' field. I would like to identify the family that has acquired any additional laptop.

The end result should have only 2 records 'family1, purchase, 3' and 'family4, purchase, 3'

family2 is not included in the result since it was a decrease of 1 and family3 is not included since there was no change in numoflaptop field.

Thanks in advance.

I am using Teradata

CodePudding user response：

Use windowing functions to calculate change in laptops and to identify most recent visit. Then use them as filters.

Select customer, visit_date
From (
    Select customer, visit_date,
       numoflaptop - ifnull(lag(numoflaptop) Over (Group by customer Order By visit_date),0) as laptopchanges,
       Row_number() Over (Group By customer Order By visit_date desc) as latest visit
    )
Where latest_visit=1 and laptopchanges=1

You did not tell us which data base manager you are using so the ifnull function might be named differently in yours. For example, in Oracle that same function is called nvl.

CodePudding user response：

Use LAG to see the previous row's value:

select customer, visit_date, reason, numoflaptop
from
(
  select
    customer, visit_date, reason, numoflaptop,
    lag(numoflaptop) over (partition by customer order by visit_date) as prev
  from mytable
) where numoflaptop > prev
order by customer, visit_date;