# Friday Sessions for Shared Experiments

> Friday vibes only. Same experiment, different users.

Canonical URL: <https://datadriven.io/problems/friday_sessions_for_shared_experiments>

Domain: SQL · Difficulty: medium · Seniority: L5

## Problem

For each Friday, count sessions where the session's user is enrolled in an experiment that at least one other user is also enrolled in. Show the date and total session count, ordered chronologically.

## Worked solution and explanation

### Why this problem exists in real interviews

Experimentation platforms ship features that hinge on cohort overlap, did this user touch any test that another user also touched. The Friday filter is dressing; the meat is recognizing that `experiments` has user-grain rows and a self-join on `exp_name` is how you find co-enrolled pairs. Get the cardinality math wrong and the session count balloons.

---

### Break down the requirements

#### Step 1: Bridge sessions to experiments by user

Each session belongs to one `user_id`; join `user_sessions` to `experiments` on `user_id` to surface every experiment that user is enrolled in. This is the first hop.

#### Step 2: Find co-enrollment via self-join

Self-join `experiments` to itself on `exp_name` with `e1.user_id <> e2.user_id`. A row survives only if at least one other user shares that experiment. Inner join filters out users in solo experiments.

#### Step 3: Restrict to Fridays and aggregate

In SQLite `strftime('%w', ts)` returns a text weekday; CAST to INTEGER and compare to 5. Group by `date(session_start)` and count rows. Order chronologically for stable output.

---

### The solution

**FRIDAY SESSIONS WITH CO-ENROLLED USERS**

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

> **Cost Analysis**
>
> The self-join on `exp_name` is the danger zone. If an experiment has K enrolled users, it produces K*(K-1) pairs, then each pair fans out across every Friday session for the original user. 50M experiment rows can explode badly; an index on `experiments(exp_name, user_id)` is the difference between minutes and hours.

> **Interviewers Watch For**
>
> Push back on the `COUNT(*)`. The current query counts a session once per co-enrolled partner per shared experiment. If the spec means distinct sessions, you need `COUNT(DISTINCT us.session_id)`. Name this trade-off; do not silently pick one.

> **Common Pitfall**
>
> Joining on `exp_id` instead of `exp_name`. With 50M experiment rows, `exp_id` is likely the per-assignment surrogate key and never collides across users. The join would always return zero rows. `exp_name` is the logical experiment identifier that groups users together.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you rewrite this without the self-join? _(Tests whether you can use a GROUP BY with HAVING COUNT(DISTINCT user_id) > 1 subquery as an alternative co-enrollment filter.)_
- What if `experiments` has duplicate rows per `(user_id, exp_name)`? _(Checks whether you anticipate over-counting and would add DISTINCT or pre-aggregate before joining.)_
- How would the query change if you wanted users co-enrolled in the same variant, not just the same experiment? _(Probes understanding of grain: adding `e1.variant = e2.variant` to the join condition narrows the cohort overlap definition.)_

> **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 convention, but `EXTRACT(ISODOW)` makes Monday 1 and Friday 5 as well, easy to conflate.

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/friday_sessions_for_shared_experiments)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.