Where the Hours Go

A medium Spark interview practice problem on DataDriven. Write the Spark code and run it against the tables, with instant feedback.

Domain

Spark

Difficulty

medium

Seniority

mid

Asked in

Synechron

Problem

We're measuring where our streaming viewers' time actually goes, and every session in user_sessions counts its full length in seconds. Total that time by the kind of device each session was watched on, as recorded in devices; that table is small, so broadcast it instead of shuffling the sessions.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a fact-to-dimension rollup dressed up as an engagement question. The seconds live in user_sessions; the category you report on lives only in devices. Anyone can write a join and a groupBy. What separates candidates is the grain: group by device_id (the key you just joined on) or tack on os_name and browser, and each device type shatters into dozens of rows, so no single total per type exists. The quieter version of the same miss is counting sessions instead of summing session_duration_sec: the shape looks right and every number is wrong.

Build it up

Step 1: Attach each session to its device

session_duration_sec sits in user_sessions, device_type sits in devices. Join on device_id with an inner join: a session with a null or unknown device_id has no type to land in, and an outer join would invent a null device_type row.

Step 2: Group on the attribute, not the key

Group on device_type alone. The join key did its job getting you to the attribute; grouping on it now answers a per-device question nobody asked. Many devices share one type, and the whole point is that their sessions collapse together.

Step 3: Sum the seconds, never count the sessions

F.sum('session_duration_sec') aliased as total_session_seconds. The question is time spent, so a count of sessions is a different metric even though it produces exactly the same five rows.

Grouped too fine

groupBy('device_type', 'os_name') or groupBy('device_id'): one row per device or per OS combo, the same type repeated many times with partial totals.

Grouped at the reported grain

groupBy('device_type') after the join: five rows, one total per type, with every device of that type folded in.

The solution

from pyspark.sql import functions as F

result = (
    user_sessions
    .join(F.broadcast(devices), on='device_id', how='inner')
    .groupBy('device_type')
    .agg(F.sum('session_duration_sec').alias('total_session_seconds'))
)

Common follow-up questions

• The `devices` table grows past what fits in executor memory. How does your plan change? (Tests whether they know broadcast has a size ceiling and can fall back to a sort-merge join with sensible partitioning.)
• One `device_type` accounts for 30 percent of all sessions. How do you keep that partition from stalling the job? (Tests skew awareness: salting the key, adaptive query execution skew handling, or a two-stage aggregation.)