Where the Minutes Go

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Fidelity

Problem

Our analytics warehouse stores one row per page view, tagged with the visitor's device and how long they stayed on the page. Total the time on page for each device type, and next to each device show the cumulative share of overall time accounted for when devices are counted up from the biggest total down through that one.

Attention piles up device by device. Find where it pools.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

What they are really asking

This is a percent-of-total leaderboard wearing a device-analytics costume, and it hides a normalization landmine. The real skill: compute each device's share of total time on page, then accumulate those shares into a running coverage figure, all against a grand total you derive in the SAME pass. Anyone can sum time per device. The separator is two things: folding the case-scrambled device strings BEFORE you group, and expressing the grand total and the running coverage so the numerator and denominator always agree. Miss the fold and the raw column hands you DESKTOP and desktop as different devices; your three-row leaderboard becomes five rows and no cumulative share ever climbs to 100.

The landmine: normalize before you aggregate

Building it up

Step 1: Collapse case, then total the time per device

In a CTE, GROUP BY LOWER(device) and SUM(dur_ms). SUM skips the NULL dwell values for free, so views with no recorded time simply contribute nothing rather than breaking the total. You now have exactly three rows: mobile, desktop, tablet, each with its total_dwell_ms.

Step 2: Get the grand total in the same expression

SUM(total_dwell_ms) OVER () with an empty window returns the total across all three device rows on every row, so you can divide each device's time by the whole without a separate total subquery to keep in sync. Because that denominator is read off the same folded rows as the numerator, the two can never drift apart.

Step 3: Accumulate the running coverage

SUM(total_dwell_ms) OVER (ORDER BY total_dwell_ms DESC) is a prefix sum: on the biggest device it is that device's time, on the second it is the top two combined, and so on. A plain GROUP BY cannot reach across rows to build this; the ordered window is what expresses it. Divide the prefix sum by the grand total, times 100, and round to two places to get the cumulative share the leaderboard has covered through each row.

The solution

WITH per_device AS (
  SELECT LOWER(device) AS device, SUM(dur_ms) AS total_dwell_ms
  FROM page_views
  GROUP BY LOWER(device)
)
SELECT
  device,
  total_dwell_ms,
  ROUND(100.0 * SUM(total_dwell_ms) OVER (ORDER BY total_dwell_ms DESC)
        / SUM(total_dwell_ms) OVER (), 2) AS running_pct
FROM per_device

Self-join plus a total subquery

To get the running coverage you self-join the grouped set to itself (each device against every device ordered at or above it), and you pull the denominator from a separate SUM subquery. Two moving parts to keep aligned, and it is easy to compute that denominator over the raw unfolded device rows by mistake, so the numerator and denominator disagree and no row lands on 100.

Two window aggregates

SUM(total_dwell_ms) OVER (ORDER BY total_dwell_ms DESC) gives the prefix sum and SUM(total_dwell_ms) OVER () gives the grand total, both off the same already-folded CTE rows. One expression, no join to align, and the denominator is guaranteed consistent with the numerator because both come from the same rows.

Common follow-up questions

• How would you keep only the devices that make up the first 80 percent of total time? (Filter on the running coverage: wrap the query and keep the rows up to where the cumulative share first crosses 80, the classic Pareto cut.)
• What if time on page were stored as separate enter and exit timestamps instead of `dur_ms`? (Derive the per-view duration first (the difference between the two timestamps), then the rest of the pipeline is unchanged.)
• How would you produce this leaderboard per month as well as overall? (Add the month to the GROUP BY and PARTITION BY that month in both windows so the totals and the coverage reset each month.)