User Engagement Summary

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

IBM

Problem

With no master user list to start from, an analyst's engagement roll-up needs one row for every identified user who appears in user_sessions, search_queries or the daily datelist snapshots in user_activity_cumulated, with a zero for any stream a user never touched. Report each user's total sessions, total search queries and the most recent last_active_date the datelist has recorded for them, blank when the datelist never tracked them, and leave out searches logged without a user attached.

Sessions plus searches. The full engagement picture.

Tables

user_sessions

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

search_queries

Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT

user_activity_cumulated

Columns: cum_row_id INTEGER, user_id INTEGER, ds TEXT, first_active_date TEXT, last_active_date TEXT, activity_bits INTEGER

Expected output

Worked solution and explanation

What this really is

This is a three-way merge of sources at different grains dressed up as an engagement report. There is no master users table, so the roster has to be built from user_sessions, search_queries and the datelist user_activity_cumulated. Anyone can count rows per user. The trick is the order of operations: collapse each source to one row per user on its own, take the union of the ids, then attach. Join the raw tables first and every survivor's counts multiply by the other sources' row counts, and every user missing from the first table vanishes.

The second trap hides in search_queries: 50 searches carry no user_id. Leave them in and they collapse into one phantom user with a pile of searches and zero sessions, a row nobody on the product side can act on.

Building it

Step 1: Count sessions per user

GROUP BY user_id over user_sessions into total_sessions. Every session there is tied to a user, so there is nothing to filter on this side.

Step 2: Count identified searches per user

Same shape over search_queries, with WHERE user_id IS NOT NULL ahead of the GROUP BY. Filtering before grouping is what stops the anonymous searches from forming a group of their own.

Step 3: Read the datelist at its latest word

user_activity_cumulated is a cumulative datelist: every daily snapshot rewrites the user's whole history, so a user has one row per ds, not one row per activity. Counting those rows means nothing, and joining them raw fans out the other counts. MAX(last_active_date) per user_id keeps only the newest thing the datelist knows about each user.

Step 4: Build the roster from all three sides

UNION the three id lists into all_users. Set semantics matter: a user present in several sources appears once, so the joins that follow cannot double them, and a user known only to the datelist still gets a row.

Step 5: Attach the per-user rows and default the gaps

LEFT JOIN all three CTEs onto all_users. A missing count comes back NULL and COALESCE(..., 0) turns it into the zero the report means. last_active_date stays NULL on purpose: a user the datelist never tracked has no date, and inventing one would be a lie.

WITH session_counts AS (
  SELECT user_id, COUNT(*) AS total_sessions
  FROM user_sessions
  GROUP BY user_id
),
query_counts AS (
  SELECT user_id, COUNT(*) AS total_queries
  FROM search_queries
  WHERE user_id IS NOT NULL
  GROUP BY user_id
),
datelist_latest AS (
  SELECT user_id, MAX(last_active_date) AS last_active_date
  FROM user_activity_cumulated
  GROUP BY user_id
),
all_users AS (
  SELECT user_id FROM session_counts
  UNION
  SELECT user_id FROM query_counts
  UNION
  SELECT user_id FROM datelist_latest
)
SELECT au.user_id,
       COALESCE(sc.total_sessions, 0) AS total_sessions,
       COALESCE(qc.total_queries,  0) AS total_queries,
       dl.last_active_date
FROM all_users au
LEFT JOIN session_counts  sc ON au.user_id = sc.user_id
LEFT JOIN query_counts    qc ON au.user_id = qc.user_id
LEFT JOIN datelist_latest dl ON au.user_id = dl.user_id;

Join the raw tables

user_sessions LEFT JOIN search_queries LEFT JOIN user_activity_cumulated ON user_id, then COUNT each side. A user with 4 sessions, 2 searches and 3 snapshots reports 24 and 24, and every search-only or datelist-only user vanishes because the roster starts from sessions.

Collapse first, then merge

Each source shrinks to one row per user_id before any join, so every join is one-to-one and all_users covers all three sides.

Where the interviewer goes next

Common follow-up questions

• How would you restrict this to the last 30 days? (Push the date predicate into each count CTE on `session_start` and `query_time` so partition pruning fires before the `GROUP BY`.)
• How would `FULL OUTER JOIN` compare in dialects that support it? (Same result with one fewer CTE, using `COALESCE(sc.user_id, qc.user_id)` as the key; engines without it still need the union pattern.)
• What if you also want users with no activity at all, from a `dim_users` table? (Replace `all_users` with `SELECT user_id FROM dim_users` so the roster is authoritative, then left-join both counts as before.)