how to select third, fourth, fifth date in sql using window function

The table rental of movies has this data:

  rental_id rental_ts   inventory_id    customer_id return_ts
  11909     2/14/2020     871           474         3/15/2020
  12222     2/14/2020     3949          22          4/18/2020

how to generate pairs of :

first rental_ts, second rental date,

second rental, third rental date, ….

second LAST rental date, last rental date,

last rental date, null?

using window functions- this is not working:

select 
customer_id,
rental_ts as first_rental_date , 
lead(rental_ts) over(partition by customer_id order by rental_ts) as second_rental_date,
lead(lead(rental_ts) over(partition by customer_id order by rental_ts)) as third_rental_date

from rental 

i expect:

customer_id , rental_date, second_rental_date, third_rental_date ... second_last_rental_date, last_rental_date

1            2/14/2020       1/14/2020      12/13/2019    12/13/2019
2            5/16/2020       5/13/2020      5/07/2020     4/29/2020

basically i need more than 1 level back – but dont know how to use it, i only used 1 lead() or lag() at work to compare current and previous dates

Answer

Don’t you need to use dynamic pivot? But if you want it this way then you can use lead(rental_ts,2), lead(rental_ts,3) etc…

 select 
customer_id,
rental_ts as first_rental_date , 
lead(rental_ts) over(partition by customer_id order by rental_ts) as second_rental_date,
lead(rental_ts,2) over(partition by customer_id order by rental_ts) as third_rental_date

from rental