Open Threads

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L4
Asked in
Microsoft

Problem

We need to track how much time employees spend in each application. HR wants daily summaries of time-per-employee-per-application, and wants to flag any employee spending more than 10 hours/day in a single application. Design the schema to capture this data.

Every minute tracked. Every app accounted for.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate models open-ended sessions safely and handles midnight-spanning windows. Both traps kill naive time-tracking schemas the moment the data hits production, and interviewers look for candidates who surface them without being asked.

Break down the requirements

Step 1: Declare the session grain

app_sessions is one row per continuous use of an application by an employee. Start and end timestamps define the session; null end means the session is still open or was abandoned.

Step 2: Make end_ts nullable

An app that crashes never emits an end event. If the column is non-null, the ingest job either loses the session or fabricates a fake end. Nullable plus a scheduled cleanup job (close sessions idle for 6 hours) is the honest design.

Step 3: Handle midnight spans in the rollup

A session from 23:45 to 00:30 contributes 15 minutes to one day and 30 minutes to the next. The daily rollup job must clip session intervals to day boundaries, or the 10-hour flag triggers on the wrong day.

Step 4: Keep employees and applications as dimensions

Conformed dimensions let HR slice by department, office, or application without rebuilding the fact. The session fact carries only surrogate keys and the two timestamps.

The solution

Below is one conceptually sound approach: raw sessions with nullable end, dimensional context on employees and applications, and daily rollups computed downstream.

`end_ts` is nullable; a cleanup job closes sessions idle beyond 6 hours without losing raw history.
  • employees: employee_id INT PK, full_name TEXT, department TEXT, hire_date DATE
  • applications: application_id INT PK, app_name TEXT, vendor TEXT, category TEXT
  • app_sessions: session_id BIGINT PK, employee_id INT FK, application_id INT FK, start_ts TIMESTAMP, end_ts TIMESTAMP, device_id TEXT
  • employees references app_sessions
  • applications references app_sessions

The analysis pattern

Daily minutes per employee per app with midnight clipping
WITH clipped AS (
    SELECT
        s.employee_id,
        s.application_id,
        d.day,
        GREATEST(s.start_ts, d.day::TIMESTAMPTZ) AS clip_start,
        LEAST(COALESCE(s.end_ts, NOW()), (d.day + INTERVAL '1 day')::TIMESTAMPTZ) AS clip_end
    FROM app_sessions s
    CROSS JOIN LATERAL generate_series(
        DATE(s.start_ts),
        DATE(COALESCE(s.end_ts, NOW())),
        INTERVAL '1 day'
    ) AS d(day)
)
SELECT
    employee_id,
    application_id,
    day,
    SUM(EXTRACT(EPOCH FROM (clip_end - clip_start)) / 60) AS minutes_used
FROM clipped
GROUP BY employee_id, application_id, day
HAVING SUM(EXTRACT(EPOCH FROM (clip_end - clip_start)) / 3600) > 10

Trade-offs and alternatives

Raw sessions plus daily clipped rollup
Raw data is preserved, rollups are recomputable, cleanup is a policy layer. Cost: rollup SQL is heavier because intervals are clipped per day.
Minute-bucket fact table
Emit a row per minute of activity so rollups are trivial GROUP BYs. Cost: volume explodes by 60x, and the ingest path has to debounce duplicate activity.

Common follow-up questions