Active Duo

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

L4

Problem

The growth team is building a cross-engagement segment of users who both make purchases and log browsing sessions on the platform. Return an alphabetical roster of the usernames for users with activity in both areas.

Shoppers who also browse. The overlap is the insight.

Tables

users

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

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date 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 really is

This is a set intersection dressed up as a growth segment. A user belongs if they appear in transactions and in user_sessions, and those two tables share nothing but user_id. Anyone can write the three-way join. The trick is seeing what it returns: every purchase paired with every session of the same user, so the rows are purchase-session pairs, not people. On this data that is 1,036 rows for 15 users, between 60 and 96 copies of each name, and a roster with alice on it 96 times is not a roster.

Step 1: Fix the grain before joining

One row per user. Say it first, because it decides everything after: whatever you read from transactions or user_sessions may only confirm a user, never add rows for them.

Step 2: Require both activities on user_id

Inner-join users to transactions and to user_sessions, both on u.user_id. An inner join drops any user missing from either side, which is exactly the both-areas rule. Reading user_sessions alone would admit 59 users; only 15 of them ever bought anything.

Step 3: Collapse the fan-out

Each surviving user now appears once per purchase-session pair, roughly 13 purchases times 5 sessions here. SELECT DISTINCT u.username folds those back to one row each. It is safe because no two of the 15 qualifying users share a username.

Step 4: Sort the roster as stored

ORDER BY u.username gives the A to Z list. Two usernames are stored with stray spaces (' amelia' and 'arjun '), and the roster returns them untouched, which is why ' amelia' sorts ahead of 'aaron42': a space comes before every letter.

The solution

SELECT DISTINCT u.username
FROM users u
JOIN transactions t ON u.user_id = t.user_id
JOIN user_sessions s ON u.user_id = s.user_id
ORDER BY u.username

SELECT u.username
FROM users u
JOIN transactions t ON u.user_id = t.user_id
JOIN user_sessions s ON u.user_id = s.user_id
ORDER BY u.username

Join both, then deduplicate

Builds every purchase-session pair per user, then throws the duplicates away with DISTINCT. It is correct and short, and it is the version most interviewers expect first.

Two existence checks

WHERE EXISTS (SELECT 1 FROM transactions t WHERE t.user_id = u.user_id) AND EXISTS (...) asks each user two yes or no questions and stops at the first match in each table. No pairs are built, so nothing needs deduplicating.

Common follow-up questions

• Only purchases with a positive `total_amount` should count. Where does that filter go, and does it change who qualifies here? (Tests filtering inside the purchase check; the seed has one refund and one voided sale, but both users have other purchases.)
• Return each user's purchase count and session count next to the username. (Tests aggregating each activity table before joining, since joining first inflates both counts by the other side's fan-out.)
• Should ' amelia' and 'arjun ' be cleaned before the roster ships, and what would that do to the sort? (Tests noticing stored whitespace and its effect on ordering and matching.)
• Now find the users who browse but never buy. (Tests flipping the intersection into an anti-join with `NOT EXISTS` or a `LEFT JOIN` null check.)