Compute each member’s current notification status
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
##### Question
You are given two tables describing LinkedIn members’ push-notification settings. Compute each member’s **current notification status as of `2020-02-29`** by combining the end-of-January baseline with the toggle actions recorded during February.
### Tables
`status` — each member’s **latest setting as of `2020-01-31`**
- `member_id` INT
- `status` VARCHAR — `'on'` or `'off'`
`actions` — all notification-toggle actions taken during **February 2020**
- `member_id` INT
- `action_date` DATE — a UTC calendar date between `2020-02-01` and `2020-02-29`
- `action` VARCHAR — `'turn_on'` or `'turn_off'`
### Assumptions
- A member performs **at most one action per day**.
- Every `actions` row occurs **after** the `2020-01-31` snapshot.
- `actions` may include **new members not present in `status`** (e.g., a member created after `2020-01-31`).
### Tasks
1. Return each member’s **current notification status as of `2020-02-29`**.
- Include **every member who appears in either table** — members only in `status`, members only in `actions`, and members in both.
- If a member has at least one February action, their status is determined by their **latest** February action (`turn_on` → `'on'`, `turn_off` → `'off'`).
- If a member has no February action, their status remains the `status` value from `2020-01-31`.
- Output columns: `member_id`, `current_status`.
2. **Follow-up A — no date column.** Suppose `actions` no longer contains `action_date`, so you cannot order the actions. Explain whether the final status can still be reconstructed, and specify what additional data, assumptions, or table design would be required to make the problem solvable.
3. **Follow-up B — no date column, valid-transition guarantee.** Now add the assumption that every recorded action is a **valid state transition** from the member’s previous state (a `turn_on` only ever follows an `'off'` state and vice versa, so no contradictory or repeated actions occur). Under that guarantee, describe (or write SQL for) how you would infer the final status as of `2020-02-29` using only the snapshot plus the **multiset** of February actions for each member.
Overview: A LinkedIn data scientist SQL screen: given an end-of-January notification-status snapshot and a February log of turn_on/turn_off actions, compute each member's current status as of 2020-02-29, keeping members from either table. Follow-ups explore reconstructing state when the action log has no date column — unsolvable in general, but recoverable via action-count parity when every action is a guaranteed valid transition.
Using PostgreSQL, return each member's current notification status as of 2020-02-29. The status table contains one baseline row per member as of 2020-01-31. The actions table contains February notification actions, with at most one action per member per day. Include every member who appears in either table. If a member has February actions, use the latest action: turn_on maps to on and turn_off maps to off. Otherwise, retain the baseline status. Return exactly member_id and current_status, ordered by member_id ascending.
Tables
status(member_id BIGINT, status VARCHAR(3))
actions(member_id BIGINT, action_date DATE, action VARCHAR(8))
Hints
- Use a window function to identify the latest February action for each member.
- Reduce the action log to one row per member before joining it to the baseline.