SQL · Intermediate · 35 min
Estimate time spent on each page
Use the next event while acknowledging what the logs cannot tell you.
Task
Estimate average page duration from page_views. A view ends at the next view by the same visitor. Exclude terminal views and gaps longer than 1,800 seconds because their duration is unknown.
Requirements
- Columns are visit_id, visitor_id, page_name, and at_seconds. visit_id is unique.
- Order a visitor’s events by at_seconds then visit_id. A same-time transition contributes zero seconds.
- Return page_name and avg_seconds rounded to two decimals, ordered by page_name.
- Do not substitute zero for a missing next event. Dates are not inferred from arrival order.
LEADEvent orderingMetric assumptions
Loading exercise…