# The Engagement Curve

> Longer sessions, more pages? Check.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The product team is studying whether longer visits actually go deeper. Sort sessions into named duration buckets and, for each bucket, show how many sessions it holds and the average pages viewed, from the highest average down.

## Worked solution and explanation

### What this really is

Beneath the 'engagement by session length' framing, this is a bucketize-then-aggregate problem, and the whole thing turns on one move: you aggregate by a column that does not exist in the table yet. The CASE expression manufactures duration_bucket, and everything downstream has to group by that derived label, not the raw session_duration_sec. Get that wrong and GROUP BY session_duration_sec hands you one row per distinct duration (thousands of them) instead of five clean buckets, with averages that answer nothing. The quieter trap is the edges: 60, 300, 900 and 1800 each belong to exactly one side, so half-open ranges are what keep a 60-second session from being counted twice.

---

### Break down the requirements

#### Step 1: Define the duration buckets

Map session_duration_sec into named ranges with a CASE expression. Use strictly-less-than cutoffs (< 60, < 300, < 900, < 1800) so every boundary second lands in exactly one bucket, and the ELSE catches everything over 30 minutes.

#### Step 2: Aggregate on the derived label

Group by the derived duration_bucket label, then compute COUNT(*) as session_count and AVG(pages_viewed) as avg_pages. AVG ignores null pages_viewed rather than reading them as zero, which is exactly what you want here.

#### Step 3: Sort highest average first

Order by avg_pages descending so the deepest-engagement bucket sits on top, with duration_bucket as a secondary key so equal averages come back in a stable, repeatable order.

---

### The solution

**CASE bucketing then group and sort**

```sql
SELECT
    CASE
        WHEN session_duration_sec < 60 THEN 'under_1min'
        WHEN session_duration_sec < 300 THEN '1_to_5min'
        WHEN session_duration_sec < 900 THEN '5_to_15min'
        WHEN session_duration_sec < 1800 THEN '15_to_30min'
        ELSE 'over_30min'
    END AS duration_bucket,
    COUNT(*) AS session_count,
    AVG(pages_viewed) AS avg_pages
FROM user_sessions
GROUP BY duration_bucket
ORDER BY avg_pages DESC, duration_bucket
```

> **Cascading CASE gives you half-open buckets for free**
>
> The CASE arms are evaluated top to bottom, so once you write '< 60' the next arm '< 300' already means '60 up to but not including 300'. That ordering is what makes the buckets half-open for free. If you instead wrote explicit ranges like 'BETWEEN 60 AND 300', the shared endpoint 300 would match two arms and the first-match rule would quietly hide the double-count from you.

> **Grouping by the raw column, not the bucket**
>
> The classic miss is GROUP BY session_duration_sec (the raw column) instead of the derived label. It runs, it looks plausible, and it returns one row per distinct duration value, so your five-bucket report explodes to thousands of rows. Group by the same CASE expression you selected (or its alias).

> **Interviewers watch for**
>
> Watch the candidate handle the null in pages_viewed without being told. AVG(pages_viewed) drops nulls, which is correct here, but a candidate who reaches for COALESCE(pages_viewed, 0) has silently redefined the metric and dragged the averages down. Knowing when NOT to coalesce is the tell.

> **Cost at scale**
>
> At 50,000,000 rows this is an unavoidable full scan: every session must be read to be bucketed, so no index on session_duration_sec helps the aggregation itself. If this report runs on a schedule, the real win is pre-aggregating into a daily rollup or materialized view keyed on the bucket, turning a 50M-row scan into a five-row read.

---

## Common follow-up questions

- A stakeholder wants sessions with missing pages_viewed treated as zero-page sessions. How does the query change, and how does it move the averages? _(Tests whether they understand AVG's null semantics versus a deliberate zero-fill, and can defend the choice.)_
- Which bucket does a session of exactly 900 seconds fall into, and how does your CASE guarantee it is not counted twice? _(Tests boundary reasoning and the ability to restate the half-open rule precisely.)_
- This report runs hourly on 50M+ sessions. How would you avoid re-scanning the whole table every time? _(Tests scaling instincts beyond a single query: incremental rollups and refresh cadence.)_

## Related

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