First Half of Page Views

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Bosch

Problem

Which page views make up the first half of our page_views log by view ID, where half is measured against however many records the table holds in total? We want every detail of each of those views plus its position number, in view ID order.

Half the data. The first half.

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 this problem really is

This is a positional cut dressed up as a log export. The skill: number every view in view_id order and cut at a boundary the data itself supplies, half of whatever page_views holds right now. Anyone can sort by view_id. The trap is writing LIMIT 100 because the table happens to hold 200 rows, or filtering view_id <= something as if ids were contiguous. Do the first and you return no rn column and a cutoff that is wrong the day the table grows. Do the second and you grab the wrong rows outright: ids here step by 53 and later batches jump past ten million.

Break down the requirements

Step 1: Establish a deterministic position

ROW_NUMBER() OVER (ORDER BY view_id) numbers every row globally. Plain LIMIT n/2 is illegal in most engines when n is itself a subquery, and it would not give you a position to return alongside the other columns.

Step 2: Derive the cutoff

(SELECT COUNT(*) / 2 FROM page_views) is a scalar subquery evaluated once over the whole table. Integer division drops the middle row on an odd total, which is the implicit contract of a first half.

Step 3: Filter and re-order

WHERE rn <= cutoff keeps the lower half. The outer ORDER BY view_id is non-negotiable because the window's ORDER BY does not bind the order of the result set after a filter.

The solution

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY view_id) AS rn
    FROM page_views
) sub
WHERE rn <= (SELECT COUNT(*) / 2 FROM page_views)
ORDER BY view_id

Common follow-up questions

Common follow-up questions

• If `view_id` has gaps from deleted rows, does a first half still mean what the business wants? (Probes whether you distinguish ordinal position from identifier value.)
• How would you return the median row when the total count is odd? (Checks understanding of integer division and the off-by-one in `n/2` versus `(n+1)/2`.)
• Rewrite this so the engine only scans `page_views` once. (Tests knowledge of windowed aggregates like `COUNT(*) OVER ()` to fold both passes into one.)