Above Average Interactions

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

Domain

SQL

Difficulty

easy

Seniority

L5

Asked in

Samsara

Problem

A downstream report shows a small set of power users drives most of the platform's session volume. Pull every user whose total session count exceeds the average session count across all users, and lay them out as a leaderboard from the most sessions down to the fewest, breaking any tie in favor of the longer-standing account with the lower user ID.

The average user is boring. Who is above?

Tables

user_sessions

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is a textbook "compare each group to a global aggregate" pattern. It tests whether a candidate can compute a per-user count, then compare each user's count against the average count across all users. The trap is that the threshold is the average of counts, not the count of rows.

Break down the requirements

Step 1: Aggregate sessions to one row per user

GROUP BY user_id with COUNT(*) AS total_sessions gives one row per user with their session count.

Step 2: Compute the average of per-user counts

Wrap the per-user counts in a subquery and take AVG(cnt). This is a single scalar that runs once. Computing AVG(COUNT(*)) directly does not work in standard SQL.

Step 3: Filter to users above that average

HAVING COUNT(*) > (scalar subquery) keeps only users whose count exceeds the average. Use strict > so users sitting exactly at the average are excluded.

Step 4: Rank from most active down

ORDER BY total_sessions DESC puts the heaviest users first, matching the "ranked from most active down" requirement.

The solution

SELECT user_id, COUNT(*) AS total_sessions
FROM user_sessions
GROUP BY user_id
HAVING COUNT(*) > (
    SELECT AVG(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM user_sessions
        GROUP BY user_id
    ) sub
)
ORDER BY total_sessions DESC, user_id

Common follow-up questions

• How would you rewrite this using a window function instead of a scalar subquery? (Tests whether the candidate knows window functions can replace correlated/scalar subqueries.)
• Session counts are usually heavy-tailed. Would the median be a more useful threshold here, and how would you compute it? (Tests awareness that the average is sensitive to outliers and the median may be more meaningful for skewed activity distributions.)
• If the team only cared about the last 30 days, what changes about the query plan and the result? (Tests partition-pruning awareness on a `session_start`-partitioned table.)
• How would you find users above the average for their signup-month cohort instead of the global average? (Tests whether the candidate can scale the pattern to per-cohort comparisons rather than a single global threshold.)