How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

What difficulty level is this interview question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at LinkedIn.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at LinkedIn during technical interviews.

Determine Final Notification Status | LinkedIn Interview Question

Q: Determine Final Notification Status

This question evaluates temporal data manipulation, state reconstruction from event logs, and handling data completeness and edge cases within the Data Manipulation (SQL/Python) domain.

You are given two tables that describe LinkedIn members' push-notification settings.

status(member_id INT, status VARCHAR)

Contains each member's latest notification setting as of 2020-01-31 .
status is either 'on' or 'off' .

actions(member_id INT, action_date DATE, action VARCHAR)

Contains all notification-toggle actions taken during February 2020.
action is either 'turn_on' or 'turn_off' .
Assume all dates are UTC calendar dates.
Assume a member can perform at most one action per day.

Tasks:

Compute each member's current notification status as of 2020-02-29 .
Include members who appear only in status , members who appear only in actions (for example, new members), and members who appear in both tables.
If a member has multiple February actions, the latest action in February determines the final status.
Follow-up: if actions did not have action_date , explain whether the final status could still be reconstructed and what additional data or table design would be required.

Required output:

member_id, current_status