Query nyc_sewer_requests for rows where closed_at::timestamp - created_at::timestamp is strictly greater than one day. Return request_id, descriptor, and numeric lag_days based on elapsed seconds divided by 86400. Do not use a calendar-date difference.
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
nyc_sewer_requestsCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| request_id | text |
| created_at | text |
| closed_at | text |
| descriptor | text |
| borough | text |
| incident_zip | text |
| geom | geometry(Geometry,4326) |
A New York service dashboard needs a fast map of one fixed week of geocoded, closed sewer-related 311 requests whose closure elapsed more than one day. The data team must query the cohort, ship a compact point layer with enough detail for a popup, and size its compressed transfer. This is a frozen teaching snapshot, not a live 311 service feed or a failure map.
Three delivery handoffs: select the review cohort in SQL, prepare an inspectable point payload, then report exact transfer sizes for the specified JSON serialization. Use a strict elapsed-time threshold and preserve request identifiers. Rounding coordinates and lag for transport must not change which requests are selected.
These are where the teaching is. Read them twice.
NYC Open Data publishes 311 service requests with identifiers, time and location fields and updates the source daily (https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2020-to-Present/erm2-nwe9). This project uses a fixed subset that excludes open and ungeocoded cases; a complaint is a report rather than a verified sewer failure, and the one-day review threshold is an exercise rule.
Each query runs on its own — explore as much as you like. The one marked Answer is what Check hands in.
2 scored, 0 informational
Review cohort rows
67%Query exposes popup fields
33%