Description

---

Your web application tracks the activities of its users using a tracking system. While a user hasn’t logged in or signed up, all the user’s actions are tracked using anonymous_id and the user_id is null, and afterwards they are tracked using the same anonymous_id and user_id. It is known that after a user logs in or signs up, the user_id is no longer null.

You are given the table tracks, which contains the following columns:

• received_at - the unique timestamp of action;
• event_name - the name of the action that was performed at this time;
• anonymous_id - the anonymous ID of user;
• user_id - the user ID, which can be null.

Your task is to find two events for each anonymous_id, which will be the column anonym_id in the returned table. Find the last event where the user was tracked only by anonymous_id (column last_null) and the first event that was tracked by user_id (column first_notnull). The resulting table should be sorted by anonym_id.

Note: It is not guaranteed that a user ever logs in or signs up. In this case, the column first_notnull should have a value of null. However, it is guaranteed that for each anonymous_id, there is at least one event where user_id equals null.

Example

For the following tables tracks

received_at	event_name	anonymous_id	user_id
2016-01-01 12:13:12	buttonClicked	1	NULL
2016-01-02 12:14:15	pageReloaded	3	NULL
2016-02-02 13:15:13	pageRendered	2	NULL
2016-02-03 13:15:23	commentWritten	3	NULL
2016-03-03 14:15:15	avatarUpdated	2	2
2016-03-04 14:15:24	statusUpdated	1	1

the output should be

anonym_id	last_null	first_notnull
1	buttonClicked	statusUpdated
2	pageRendered	avatarUpdated
3	commentWritten	NULL

• [execution time limit] 10 seconds (mysql)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/*Please add ; after each select statement*/
CREATE PROCEDURE trackingSystem()
BEGIN
    SELECT t3.anonym_id as anonym_id, last_null, first_notnull FROM
	(SELECT anonymous_id as anonym_id, event_name as last_null
    FROM tracks JOIN (
        SELECT MAX(received_at) last
        FROM tracks
        WHERE user_id IS NULL
        GROUP BY anonymous_id) t ON received_at = last) t3
    LEFT JOIN (
    SELECT anonymous_id as anonym_id, event_name as first_notnull
    FROM tracks JOIN (
        SELECT MIN(received_at) first
        FROM tracks
        WHERE user_id IS NOT NULL
        GROUP BY anonymous_id) t2 ON received_at = first) t4
    ON t3.anonym_id  = t4.anonym_id
    ORDER BY anonym_id;
END