Rate consecutive row pairs in SQLite

Question

I have data in a SQLite table that looks like this:

user_id     event_date
----------  ----------
1000001     2008-01-01
1000001     2008-03-13
1000001     2008-07-04
1000002     2007-01-06
1000002     2008-01-01
1000002     2009-06-01
1000002     2010-12-11

For each of them, user_idI would like to choose the largest time interval between pairs of consecutive event_dates. For example, in this specific data, user 1000001 has two spaces: 72 days between 2008-01-01 and 2008-03-13 and 113 days between 2008-03-13 and 2008-07-04, so the request should display 113 for user 1000001 User 1000002 has three spaces; the largest is 558 days.

Is it possible? Should I pre-calculate the time intervals and store them with data, or select everything after the process? I would like this to be possible to do directly with the data, as shown and directly in the database. Am I abusing SQL, expecting it to be able to process this data as a list?
Thank.

Answer 1

If you cannot change the data model, you can use:

SELECT user_id, MAX(event_date - prior_event_date)
  FROM (SELECT t.user_id, t.event_date, MAX(prior.event_date) AS prior_event_date
          FROM your_table AS t
               JOIN your_table AS prior ON (t.user_id=prior.user_id
                                            AND prior.event_date < t.event_date)
         GROUP BY t.user_id, t.event_date) AS events
 GROUP BY user_id;

If you want 0s to be included, use LEFT JOIN.