Suspected Bot Sessions

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

L3

Asked in

ESPN

Problem

Sessions shorter than 100 seconds get flagged as potential bot activity. Return the session ID, user ID, and session duration for each suspect session in 2026.

Five seconds or less. Probably a bot.

Tables

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 really is

This is a two-condition filter dressed up as bot detection, and the second condition is the one people lose. Everyone reads 'shorter than 100 seconds' and writes session_duration_sec < 100. The year sits at the tail of the sentence and gets treated as flavor text. On this data that costs you: user_sessions holds sessions from 2023 through 2026, and one session from January 2025 ran 91 seconds. Drop the year and the 2026 bot report carries a row from last year's traffic.

Step 1: Read the year as a filter, not a caption

'Each suspect session in 2026' carries two conditions: suspect (under 100 seconds) and in 2026. Both must hold for the same row, so they join with AND in one WHERE.

Step 2: Pull the year out of session_start

session_start is TEXT shaped like '2026-02-02 01:00:00'. strftime('%Y', session_start) returns the four-character year as text, so the comparison is against the quoted '2026', not the number.

Step 3: Keep the cutoff strict

'Shorter than 100' means < 100: a session of exactly 100 seconds is not flagged. No session here sits on the cutoff, so the data will not catch a <=, but the interviewer will ask, and you want to have chosen on purpose.

Step 4: Return the three columns and let NULLs fall out

Project session_id, user_id and session_duration_sec. Sessions with no recorded length need no special handling: NULL < 100 is unknown, and WHERE keeps only rows where the condition is true.

SELECT session_id, user_id, session_duration_sec
FROM user_sessions
WHERE session_duration_sec < 100 AND strftime('%Y', session_start) = '2026'

Duration only

WHERE session_duration_sec < 100 returns four rows: the three 2026 sessions plus a 91-second session from January 2025. It looks right because every row really is short.

Duration and year

Adding strftime('%Y', session_start) = '2026' keeps the 53, 76 and 99 second sessions and nothing else. Every row is short and inside the reporting year.

Common follow-up questions

• If `session_start` were stored in mixed formats, how would you reliably extract the year? (Tests whether the candidate validates text timestamps before slicing them.)
• Would an index on `session_duration_sec` alone help, or should `session_start` lead the index? (Tests selectivity reasoning: one year out of several is usually the narrower cut.)
• If the rule becomes 'fewer than 3 pages viewed AND under 100 seconds', how does the filter change? (Tests adding a third condition on `pages_viewed` without disturbing the other two.)