# The Company You Keep > Only experiments with a crowd count. Find the Friday sessions they drove. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## Problem On our experimentation platform we only care about users who share an experiment with at least one other user. For each Friday, count how many sessions those users ran, listed from the earliest date. ## 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 **FRIDAY SESSIONS FOR CO-ENROLLED USERS** ```sql 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 ``` > **Common Pitfall** > > The version that over-counts: INNER JOIN experiments onto us.user_id, then self-join to a second experiments row, then COUNT(*). Each session is now counted once per co-enrolled partner per shared experiment, so a single Friday session can report as 44. The DISTINCT user_id CTE is the fix: it reduces co-enrollment to a yes or no per user before you ever touch a session row. > **Interviewers Watch For** > > The tell is grain discipline. A strong candidate says out loud 'I need existence, not multiplicity' and reaches for a semi-join or a DISTINCT collapse. A weaker one writes the natural chain of joins and never notices COUNT(*) is now counting join rows instead of sessions. **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. > **Cost Analysis** > > user_sessions is 120M rows and experiments is 50M. An index on experiments(exp_name, user_id) makes both the self-join and the co-enrollment lookup cheap, and collapsing to DISTINCT user_id shrinks the right side of the session join to at most the number of enrolled users, so the large session scan probes a compact set instead of exploding. > **Pick the right join key** > > Joining on `exp_id` instead of `exp_name` is a quiet way to get zero rows. With 50M experiment rows, `exp_id` is the per-assignment surrogate key and never collides across users, so the self-join finds no pairs. `exp_name` is the logical experiment that actually groups users together. --- ### 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.)_ > **Weekday Indexing in SQLite** > > SQLite's strftime('%w', ...) returns 0 for Sunday through 6 for Saturday, so Friday is 5. Postgres EXTRACT(DOW FROM ...) matches that, but EXTRACT(ISODOW) shifts the week to Monday as 1, where Friday also lands on 5, which is easy to conflate when porting. ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_company_you_keep) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.