Katkat
Back

Compute each member’s current notification status

LinkedInData Scientist · Technical Screen
EasyData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.

  1. 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.

  2. 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.