The Gaps Between Clicks

A hard Data Modeling interview practice problem on DataDriven. Write and execute real data modeling code with instant feedback.

Domain

Data Modeling

Difficulty

hard

Seniority

mid

Problem

We run a consumer web and mobile product that emits a high-volume clickstream: every page view, tap, and scroll lands as an event tied to a device, and a visitor is usually anonymous for a while before they sign in. Analysts need visit-level analysis (how long a session runs, how many events it holds, where it drops off) as well as daily engagement rolled up by page and device, where a session is activity with no more than a thirty-minute gap between consecutive events. Design a model that serves both the visit-level questions and the daily rollups, keeps a person's anonymous and signed-in activity connected, and credits each visit to whoever held the device during the window when that activity happened, since a shared device can pass from one person to another and each of them owns it only for a bounded stretch of time.

Summary

A visit is a story with a start and an end. Draw the model that finds where one ends and who was really there.

Practice This Problem

Solve this Data Modeling problem with real code execution. DataDriven runs your solution and checks it automatically.