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)

Deze oefening maakt deel uit van de cursus

Exploratory Data Analysis in SQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- 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);

Code bewerken en uitvoeren