Possible to rewrite this query as a join? I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:

SELECT 
   events.*, 
   (SELECT MAX(updated_at) 
    FROM   events e 
    WHERE  e.user_id = events.user_id 
           AND type = 'follow' 
           AND e.updated_at &lt; events.updated_at 
           AND events.type = 'unfollow'
   ) AS last_follow_date 
FROM   events 
WHERE  user_id = 1; 


Database structure:

CREATE TABLE `events` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `user_id` int(10) unsigned NOT NULL,
    `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower_pk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    KEY `events_user_id_foreign` (`user_id`),
    CONSTRAINT `events_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


Some data:

INSERT INTO `users` (`id`, `name`, `email`)
VALUES (1, 'Aydin Hassan', 'email@website.com');

INSERT INTO `events` (`id`, `created_at`, `updated_at`, `user_id`, `type`, `follower`, `follower_pk`)
VALUES 
(1, '2015-01-01 00:00:00', '2015-01-01 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(2, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'unfollow', 'annie.leibovitz', '10'),
(3, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'follow', 'edward.weston', '11'),
(5, '2015-10-10 00:00:00', '2015-10-10 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(4, '2017-01-01 10:10:00', '2017-01-01 10:10:00', 1, 'unfollow', 'annie.leibovitz', '10');


The table stores events regarding followers of a user on a makeshift social platform. The query is adding (if the event is a unfollow type) the last date that the follower started following the user.

The correct results should be:

+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
| id |     created_at      |     updated_at      | user_id |   type   |    follower     | follower_pk |  last_follow_date   |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
|  1 | 2015-01-01 00:00:00 | 2015-01-01 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
|  2 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-01-01 00:00:00 |
|  3 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | follow   | edward.weston   |          11 | NULL                |
|  4 | 2017-01-01 10:10:00 | 2017-01-01 10:10:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-10-10 00:00:00 |
|  5 | 2015-10-10 00:00:00 | 2015-10-10 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+


As a bonus question: Is there maybe a better way to store and query this data?
You can get what you want with the following JOINed query, which ressembles very much your original one:

SELECT
    e.id, e.created_at, e.updated_at, e.user_id, e.type, e.follower, e.follower_pk, 
    max(e2.updated_at) AS last_follow_date
FROM
    events e
    LEFT JOIN events e2 
        ON      e2.user_id = e.user_id 
            AND e2.type = 'follow' 
            AND e2.updated_at &lt; e.updated_at
            AND e.type = 'unfollow' 
WHERE
    e.user_id = 1
GROUP BY
    e.id 
ORDER BY
    e.id, e.updated_at ;


With regard to performance, your queries will benefit from an index such as:

-- Index to improve performance
CREATE INDEX idx_events_user_id_type_updated_at
    ON events (user_id, type, updated_at) ;


The three columns user_id, type and updated_at appear in the JOIN condition. The order in which you want them is user_id (you make an = comparison, and is supposed to have high cardinality --i.e.: very many values--), type (you make an = comparison, cardinality = 2), updated_at checked with an inequality... MySQL is not good enough to use it as part of the index seek, but will make the index a covering one, and that will save from checking the original table.



Alternative: a slight twist on your original query, might save some time:

SELECT 
   e1.*, 
   case when type = 'unfollow' then
      (SELECT MAX(e2.updated_at) 
       FROM   events e2 
       WHERE  
                  e2.user_id = e1.user_id 
              AND e2.type = 'follow'
              AND e2.updated_at &lt; e1.updated_at
       )
   else NULL end AS last_follow_date
FROM   events e1
WHERE  e1.user_id = 1; 


You can check everything at dbfiddle here