The Company You Keep

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

L5

Asked in

Walmart

Problem

Count the Friday sessions in user_sessions as an integer tally per date, earliest date first, for the users the experimentation team is tracking. Those are the users who share an experiment in experiments with at least one other user.

Only experiments with a crowd count. Find the Friday sessions they drove.

Tables

user_sessions

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

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

Expected output

Worked solution and explanation

What this is really testing

Beneath the Friday framing this is a semi-join: does this session's user belong to any experiment that a second user also joined? The metric lives at session grain, one row per session, but the co-enrollment test lives at experiment grain. The trap is letting those two grains collide. If you join experiments straight onto user_sessions and COUNT(*), one Friday session gets counted once for every peer in every experiment its user touched, and the total balloons into numbers no reader could predict. The move that saves you is to collapse co-enrollment down to a set of qualifying users first, then count each session exactly once.

Build it in layers

Step 1: Find the experiments with a crowd

Self-join experiments to itself on exp_name with e1.user_id <> e2.user_id. An exp_name survives only if two different users share it. Wrap it in SELECT DISTINCT exp_name so a popular experiment does not repeat downstream.

Step 2: Collapse co-enrollment to a set of users

Join those experiments back to experiments and take DISTINCT user_id. This is the step that protects the count: every co-enrolled user now appears exactly once, so the later join to sessions is one to one and cannot fan out.

Step 3: Filter Fridays and count sessions

In SQLite strftime('%w', session_start) returns a text weekday, 0 for Sunday through 6 for Saturday, so CAST to INTEGER and compare to 5 for Friday. Join the qualifying users to their sessions, group by date(session_start), COUNT(*), and order chronologically.

The solution

WITH shared_experiments AS (
  SELECT DISTINCT e1.exp_name
  FROM experiments e1
  INNER JOIN experiments e2
    ON e1.exp_name = e2.exp_name
   AND e1.user_id <> e2.user_id
),
co_enrolled_users AS (
  SELECT DISTINCT e.user_id
  FROM experiments e
  INNER JOIN shared_experiments s
    ON e.exp_name = s.exp_name
)
SELECT date(us.session_start) AS session_date,
       COUNT(*) AS total_sessions
FROM user_sessions us
INNER JOIN co_enrolled_users cu
  ON us.user_id = cu.user_id
WHERE CAST(strftime('%w', us.session_start) AS INTEGER) = 5
GROUP BY date(us.session_start)
ORDER BY session_date

Join then COUNT(*)

experiments fans out onto every matching session, so COUNT(*) counts join rows and one session becomes many. The answer inflates and swings with how many peers each experiment has.

Semi-join on distinct users

co-enrollment collapses to one row per user, the session join stays one to one, and COUNT(*) counts sessions. The answer is the real Friday session tally.

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you find the shared experiments without a self-join? (Checks whether the candidate knows the self-join and a GROUP BY `exp_name` HAVING COUNT(DISTINCT `user_id`) > 1 filter are two routes to the same co-enrollment set, and can weigh cost.)
• What if experiments has duplicate rows for the same user and experiment? (Probes grain awareness: duplicate (`user_id`, `exp_name`) rows would re-inflate even the collapsed set unless you dedup before counting.)
• How would the query change if users had to share the same variant, not just the same experiment? (Tests tightening the cohort definition: adding variant to the shared-experiment key narrows co-enrollment from same experiment to same variant.)