Compute each member’s current notification status

You have two tables describing LinkedIn members’ push notification settings.

Tables

status — each member’s latest setting as of 2020-01-31

• member_id INT (PK)
• status VARCHAR — values: 'on' or 'off'

actions — all setting change actions during Feb 2020

• member_id INT
• date_sk DATE
• action VARCHAR — values: 'turn_on' or 'turn_off'

Assumptions:

• A member performs at most one action per day .
• actions may include new members not present in status (e.g., a member created after 2020-01-31).

Task

Return each member’s current notification status as of 2020-02-29.

• Include all members who appear in either table .
• If a member has at least one action in Feb, their status is determined by their latest action in Feb.
• If a member has no Feb action, their status remains their status value from 2020-01-31.

Output:

• member_id
• current_status

Follow-up

How would you handle it if the actions table did not have date_sk (i.e., you had no reliable way to order actions)? Specify what additional data/assumptions are required to make the problem solvable.