# Where the Minutes Go > Attention piles up device by device. Find where it pools. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem Our analytics warehouse stores one row per page view, tagged with the visitor's device and how long they stayed on the page. Build a leaderboard of device types by total time on page, biggest first, and next to each device show the cumulative share of overall time the leaderboard has accounted for through that row. ## 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 > **The device column is case-scrambled** > > The sample rows already show it: desktop, DESKTOP, Mobile, mobile, tablet. Group on the raw device and each real device splits across rows, so mobile's time lands half under 'Mobile' and half under 'mobile'. Apply LOWER(device) in BOTH the SELECT and the GROUP BY to collapse them into the three real device types first, and do it inside a CTE so everything downstream sees clean keys. ### 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 **One CTE, two windows, one consistent pass** ```sql 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 ORDER BY total_dwell_ms DESC ``` *LOWER folds the device case; the two SUM windows give grand total and running coverage off the same folded rows, so numerator and denominator always agree.* > **Two windows read the same folded rows** > > The plain OVER () and the OVER (ORDER BY ... DESC) both run over the three-row CTE. There is no self-join of the grouped set to itself and no second subquery re-summing the total. The expensive GROUP BY happens once, collapsing billions of rows to three, and the windows operate on that tiny grouped result, so the cumulative share is both cheap and provably consistent with the folded grouping. > **What the interviewer is watching** > > Did you LOWER the device on both sides of the GROUP BY? Did you let SUM ignore the NULL dur_ms instead of coalescing it to something that skews the total? And did you round to match the two-decimal shares in the preview? Candidates who group on the raw device produce five rows and never notice the shares do not reach 100; that single tell separates a careful engineer from a fast one. **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. > **Why it scales** > > The base table is partitioned by day across billions of page views. The GROUP BY LOWER(device) collapses it to three rows before any window runs, so the windows themselves are trivial and the whole cost is the single grouping scan. A covering index on (device, dur_ms) lets the planner aggregate without touching the wide row; the cumulative sum then sorts three rows, not billions. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_minutes_go) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.