Determine Final Notification Status

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.

Q: 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.

Question

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

Determine Final Notification Status

Quick Overview

Comments (0)