Determine current notification status

You are given two tables about members' push-notification settings.

Table 1: status_snapshot

• member_id INT
• status STRING — either 'on' or 'off'

This table stores each member's latest known notification setting as of 2020-01-31 23:59:59 UTC.

Table 2: actions

• member_id INT
• action_date DATE — UTC calendar date between 2020-02-01 and 2020-02-29
• action STRING — either 'turn_on' or 'turn_off'

Assumptions:

• Each row in actions is a notification-setting action that occurs after the snapshot in status_snapshot .
• A member can have at most one action per day.
• Members may appear only in actions (for example, new members created after the snapshot date).
• The member's latest February action determines their final status; if a member has no February actions, keep the snapshot status.

Write SQL to return each member's current notification status as of 2020-02-29 23:59:59 UTC.

• Output columns: member_id, current_status
• Include every member who appears in either table.

Follow-up: Suppose actions no longer contains action_date. Under the assumption that every recorded action is a valid state transition from the member's previous state (so contradictory repeated actions such as turn_on when already on do not occur), write SQL or describe how you would infer the final status as of 2020-02-29 using only the snapshot plus the multiset of February actions.