Creating the SQL script that tracks progress of an object

Question

I have a table with this schema:

Fruit   Truck ID  Bucket ID        Date         
------   -----    ---------     ----------
Apple      1         101        2018/04/01
Apple      1         101        2018/04/10
Apple      1         112        2018/04/16
Apple      2         782        2018/08/18
Apple      2         782        2018/09/12
Apple      1         113        2019/09/12
Apple      1         113        2019/09/21

My goal is to write an SQL script that returns the start and end dates of each truck & bucket pair for each fruit. The intended result is below:

Fruit   Truck ID  Bucket ID     Start Date     End Date     
------   -----    ---------     ----------    ----------
Apple      1         101        2018/04/01    2018/04/16
Apple      1         112        2018/04/16    2018/08/18
Apple      2         782        2018/08/18    2018/09/12
Apple      1         113        2019/09/12    2019/09/21

I have tried solving this through lag/lead window functions, but it the dates are not correct. Is there another method of solving this using window functions or do I have to create sub queries for this?

Answer 1

I think you want aggregation and window functions:

select fruit, truck_id, bucket_id,
    min(date) start_date,
    lead(min(date), 1, max(date)) over(partition by fuit order by min(date)) end_date
from mytable
group by fruit, truck_id, bucket_id