# The Threshold

> Every visit begins with three doors. Find the ones most often opened.

Canonical URL: <https://datadriven.io/problems/the_threshold>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We're studying the first impression our product makes, so for each visitor we look only at the earliest three pages they opened, in the order they viewed them. Across all visitors, surface the pages that appear most often in those opening views, keeping the three most common and any page tied at the third position.

## Worked solution and explanation

### What this problem is really testing

This is a per-user ranking problem wearing a popularity-metric costume. Anyone can COUNT pages and sort them; the separating move is the PARTITION BY user_id on ROW_NUMBER, which gives every user their own 1, 2, 3 sequence so you count first-session pages instead of all-time traffic. Miss the partition and you rank views globally: the leaderboard becomes whatever the earliest-registered handful of users happened to click, and the metric stops measuring onboarding entirely.

---

### Break down the requirements

#### Step 1: Number views per user by viewed_at

Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY viewed_at ASC) so each user gets a 1, 2, 3, ... sequence independent of every other user. The PARTITION BY is what makes this a per-user first-3, not a global first-3.

#### Step 2: Filter to view_order <= 3 before aggregating

Apply the cutoff in a CTE that pulls only the prefix rows, then GROUP BY page_url and COUNT(*) to get appearance_count across all users. Filtering before the aggregate keeps late-funnel pages out of the leaderboard. In the sample, every user's fourth view is /checkout, which sits outside the first-3 prefix and never reaches the count, even though it would otherwise top the list with 5 appearances.

#### Step 3: Use DENSE_RANK to keep ties at the cutoff

DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) lets you keep every page that ties at the cutoff. In the sample, /pricing and /about both land at 3 appearances and share the third position, so both survive. A LIMIT 3 would silently drop one of them, and the prompt explicitly says to include all of them.

---

### The solution

**First-3 prefix, then dense rank with tie inclusion**

```sql
WITH ranked_views AS (
  SELECT user_id, page_url, viewed_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY viewed_at ASC) AS view_order
  FROM page_views
),
page_counts AS (
  SELECT page_url, COUNT(*) AS appearance_count,
         DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM ranked_views
  WHERE view_order <= 3
  GROUP BY page_url
)
SELECT page_url, appearance_count
FROM page_counts
WHERE rnk <= 3
ORDER BY appearance_count DESC
```

> **Cost Analysis**
>
> page_views has 600M rows and 10M users with zipf skew. The PARTITION BY user_id window is the dominant cost; an index on (user_id, viewed_at) lets the planner stream rows already sorted and avoids a sort-per-partition. The second CTE collapses to about 40k page_url groups, so the rank step is cheap.

> **Interviewers Watch For**
>
> Did you partition by user_id (not globally rank), use DENSE_RANK (not ROW_NUMBER) for ties, and filter to view_order <= 3 before aggregating? Candidates who LIMIT 3 at the end fail the explicit tie rule in the prompt.

> **Common Pitfall**
>
> Ordering by viewed_at without PARTITION BY user_id ranks views globally, so the top of the list is just the earliest registered users. The per-user PARTITION BY is non-negotiable for a first-view metric.

---

## Common follow-up questions

- How would you handle users who only ever viewed one page? _(They contribute exactly one row to the first-3 prefix; the COUNT still works. No special case needed, but interviewers may probe whether you noticed.)_
- What changes if 'first 3' should be 'first 3 distinct pages'? _(Switch ROW_NUMBER to operate on a deduped per-user CTE (DISTINCT user_id, page_url, MIN(viewed_at)) before ranking, so a user who reloads the same page doesn't burn their prefix slots.)_
- Would you recompute this nightly or maintain it incrementally? _(First-N-per-user is order-dependent on early rows, so an incremental aggregate has to track each user's current prefix. Most teams just recompute against a partitioned table.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_threshold)
- [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.