sql - Maximum difference between MORE than two dates in SQL?

Question

Can someone help me with this? It is to perform a query in SQL on a REDSHIFT database. The table has the data loaded as follows. A client, for example, has a date field that determines her activity in an application:

ID	DATE
customer_1	2019-01-18
customer_1	2019-08-25
customer_1	2020-02-25
customer_1	2020-06-18
customer_2	2019-08-21
customer_2	2020-02-13
customer_2	2020-06-30
customer_3	2020-01-18
customer_3	2020-04-04

I need to group by client and know their MAXIMUM period of inactivity. I call MAXIMUM inactivity the longest period between TWO dates in which it did not interact. To do this with SQL, you should go through all the dates of the same user (customer_x) which is PK. I do not know if you can help me think about it since I have not been able to do it alone. Thanks.

ID	Maximum inactivity (in days)
customer_1	94
customer_2	60
customer_3	45

标签： sqlamazon-web-servicesamazon-redshift

Answer 1

你可以用LAG()这个。

例如：

select
  id,
  max(inactivity) as max_inactivity
from (
  select
    id,
    datediff(day, lag(date) over(partition by id order by date), date)
      as inactivity
  from t
) x
group by id