Longest gap

What is the longest time between Evanston 311 requests being submitted?

Recall the syntax for lead() and lag():

lag(column_to_adjust) OVER (ORDER BY ordering_column)
lead(column_to_adjust) OVER (ORDER BY ordering_column)

This exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Exercise instructions

Select date_created and the date_created of the previous request using lead() or lag() as appropriate.
Compute the gap between each request and the previous request.
Select the row with the maximum gap.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Compute the gaps
WITH request_gaps AS (
        SELECT date_created,
               -- lead or lag
               ___(date_created) OVER (___) AS previous,
               -- compute gap as date_created minus lead or lag
               date_created - ___(date_created) OVER (___) AS gap
          FROM evanston311)
-- Select the row with the maximum gap
SELECT *
  FROM request_gaps
-- Subquery to select maximum gap from request_gaps
 WHERE gap = (SELECT ___
                FROM request_gaps);

Edit and Run Code