Power Users by Session Activity

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

L3

Asked in

eBay

Problem

Neither a pile of quick visits nor a couple of marathon sessions makes someone a power user: a power user is an active account in users with at least 3 sessions in user_sessions that average at least 25 pages viewed per session. List each power user's id, username, session count and total pages viewed, most total pages first.

More sessions. More time. The power users.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

What this is really testing

This is a group-level filter dressed up as a marketing case study. Both bars, at least 3 sessions and at least 25 pages per session on average, describe a user's whole history, and neither exists until that user's user_sessions rows collapse into one. Anyone can write the join. What separates candidates is where each filter lives: account_status describes one users row, so it filters before grouping; the count and the average describe the group, so they filter after it. Push the 25-page bar down onto individual sessions and the light sessions quietly vanish: averages inflate, counts shrink, and frequent visitors who really average under 25 slip into the case study.

Build it in order

Step 1: Join users to sessions

JOIN user_sessions us ON u.user_id = us.user_id pairs each user with their sessions. An inner join is right because a user with no sessions can never clear a session bar.

Step 2: Scope to active accounts

WHERE u.account_status = 'active' is a fact about a single users row, so it belongs before grouping. Inactive, suspended and pending accounts never enter a group.

Step 3: Collapse each user's sessions

GROUP BY u.user_id, u.username gives one row per user. COUNT(us.session_id) and SUM(us.pages_viewed) become the reported columns; AVG(us.pages_viewed) is needed only for the bar.

Step 4: Test both bars on the groups, then sort

HAVING COUNT(us.session_id) >= 3 AND AVG(us.pages_viewed) >= 25 tests both bars against the finished groups, then the rows sort by total_pages DESC. The bars catch different people: someone with two heavy sessions clears the average but not the count, and a frequent visitor with light sessions clears the count but not the average.

The solution

SELECT
    u.user_id,
    u.username,
    COUNT(us.session_id) AS session_count,
    SUM(us.pages_viewed) AS total_pages
FROM users u
JOIN user_sessions us ON u.user_id = us.user_id
WHERE u.account_status = 'active'
GROUP BY u.user_id, u.username
HAVING COUNT(us.session_id) >= 3 AND AVG(us.pages_viewed) >= 25
ORDER BY total_pages DESC

Filter sessions, then group

WHERE us.pages_viewed >= 25 discards light sessions before grouping. A frequent visitor whose real average is under 25 still clears the count on the sessions left behind, and every surviving session_count and total_pages is understated.

Group, then filter the groups

HAVING AVG(us.pages_viewed) >= 25 averages all of a user's sessions, zero-page visits included. Light visitors are correctly left out, and the reported totals are the real ones.

SELECT
    u.user_id,
    u.username,
    COUNT(us.session_id) AS session_count,
    SUM(us.pages_viewed) AS total_pages
FROM users u
JOIN user_sessions us ON u.user_id = us.user_id
WHERE u.account_status = 'active'
  AND us.pages_viewed >= 25
GROUP BY u.user_id, u.username
HAVING COUNT(us.session_id) >= 3
ORDER BY total_pages DESC

Common follow-up questions

• If marketing wanted the top 1% of users by `total_pages` instead of fixed bars, how would you restructure the query? (Tests replacing fixed bars with a percentile computed over the per-user aggregates.)
• If only the last 30 days of sessions mattered, where does that filter go and what does the planner gain? (Tests partition pruning on `session_start`.)
• How would you list every active user with a flag saying whether they qualify, including users with no sessions? (Tests a `LEFT JOIN` with `COALESCE` and turning the bars into a flag column.)