Skip to content
Advertisement

Is it possible to look at two consecutive rows and determine the difference in time between the two using SQL?

I am relatively new to SQL, so please bear with me! I am trying to see how many customers make a purchase after being dormant for two years. Relevant fields include cust_id and purchase_date (there can be several observations for the same cust_id but with different dates). I am using Redshift for my SQL scripts.

I realize I cannot put the same thing in for the DATEDIFF parameters (it just doesn’t make any sense), but I am unsure what else to do.

Advertisement

Answer

I think you want lag(). To get the relevant tickets:

If you want the number of customers, use count(distinct):

Note that these do not use DATEDIFF(). This counts the number of boundaries between two date values. So, 2018-12-31 and 2019-01-01 have a difference of 1 year.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement