# User Engagement Totals

> Per-user engagement. The totals.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

For each user, calculate total session duration converted to the nearest whole minute and count of unique content items viewed (zero if none). Show user ID, total minutes, and unique content count.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests combining data from two tables with different granularities and applying unit conversion. Interviewers check whether you can LEFT JOIN to handle users with zero content views and convert seconds to minutes correctly.

---

### Break down the requirements

#### Step 1: Aggregate session duration per user

`SUM(session_duration_sec)` from `user_sessions` grouped by `user_id`, then convert to whole minutes with `ROUND(... / 60.0)`.

#### Step 2: Count distinct content items viewed

`COUNT(DISTINCT page_url)` from `page_views` gives unique content items per user. Use COALESCE to show 0 for users with no views.

#### Step 3: Combine with pre-aggregation

Pre-aggregate each table separately, then LEFT JOIN to avoid the fan-out trap.

---

### The solution

**Pre-aggregate and join for multi-source engagement**

```sql
SELECT
    s.user_id,
    ROUND(s.total_secs / 60.0) AS total_minutes,
    COALESCE(p.unique_content, 0) AS unique_content_count
FROM (
    SELECT user_id, SUM(session_duration_sec) AS total_secs
    FROM user_sessions
    GROUP BY user_id
) s
LEFT JOIN (
    SELECT user_id, COUNT(DISTINCT page_url) AS unique_content
    FROM page_views
    GROUP BY user_id
) p ON s.user_id = p.user_id
```

> **Cost Analysis**
>
> Two independent aggregations: 40M sessions and 400M page views. The sessions aggregate produces 3M rows; the page views aggregate produces 8M rows (with a DISTINCT on 35K page URLs per user). The join is efficient on pre-aggregated data.

> **Interviewers Watch For**
>
> Using ROUND for "nearest whole minute" as specified. Truncation (CAST to INTEGER) or FLOOR would round down, which is not what the prompt asks.

> **Common Pitfall**
>
> Joining the raw tables directly instead of pre-aggregating. A user with 10 sessions and 100 page views would produce 1,000 rows, inflating both the duration sum and the content count.

---

## Common follow-up questions

- What if session_duration_sec has NULL values? _(SUM ignores NULLs, but the total could be lower than expected. COALESCE each value to 0 before summing.)_
- How would you include users who have page views but no sessions? _(Change to a FULL OUTER JOIN or use a master users table.)_
- What if 'content item' is identified by content_id instead of page_url? _(If the page_views table has a content_id column, use that for DISTINCT. If not, join to a content mapping table.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/user_engagement_totals)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.