By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `student_info`
(`student_id` INT NOT NULL,
`date_registered` DATE DEFAULT NULL,
PRIMARY KEY (`student_id`));
INSERT INTO `student_info` VALUES
(255193, '2021-12-01'),(255194, '2021-12-01'),(255196, '2021-12-01'),
(255198, '2021-12-01'),(255199, '2021-12-01'),(255200, '2021-12-01'),
(255201, '2021-12-01'),(255203, '2021-12-01'),(255204, '2021-12-01'),
(255205, '2021-12-01');
-- Full dataset has 40979 records
CREATE TABLE `student_engagement`
(`student_id` INT DEFAULT NULL,
`date_watched` DATE DEFAULT NULL);
INSERT INTO `student_engagement` VALUES
(255200, '2021-12-01'),(255201, '2021-12-02'),(255201, '2021-12-06'),
(255201, '2021-12-08'),(255201, '2022-10-05'),(255203, '2022-03-08'),
(255203, '2022-03-17'),(255203, '2022-03-23'),(255203, '2021-12-01'),
(255203, '2022-01-25'),(255203, '2022-01-26'),(255203, '2022-01-27'),
(255203, '2022-02-08'),(255203, '2022-02-09'),(255203, '2022-02-21'),
(255203, '2022-02-22'),(255203, '2022-02-23'),(255204, '2021-12-01'),
(255204, '2021-12-02'),(255204, '2021-12-03'),(255204, '2021-12-14'),
(255204, '2021-12-17'),(255204, '2021-12-07'),(255204, '2021-12-05'),
(255204, '2021-12-09'),(255204, '2021-12-23'),(255204, '2022-01-11'),
(255204, '2022-01-18'),(255204, '2022-03-05'),(255204, '2022-06-19'),
(255204, '2022-07-06'),(255204, '2021-12-04'),(255204, '2021-12-18'),
(255204, '2021-12-20'),(255204, '2021-12-29'),(255204, '2022-03-28'),
(255204, '2022-03-31'),(255204, '2022-07-17'),(255204, '2022-07-21'),
(255204, '2022-07-31'),(255204, '2021-12-12'),(255204, '2021-12-13'),
(255204, '2021-12-21'),(255204, '2021-12-27'),(255204, '2022-01-07'),
(255204, '2021-12-08'),(255204, '2022-01-27'),(255205, '2021-12-01'),
(255205, '2021-12-02'),(255205, '2021-12-07'),(255205, '2022-01-22'),
(255205, '2022-04-05');
-- Full dataset has 74246 records
Records: 10 Duplicates: 0 Warnings: 0
Records: 52 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
/*Your base query returning 5 records*/
SELECT se.student_id,
si.date_registered,
MIN(se.date_watched) AS first_date_watched,
MIN(sp.date_purchased) AS first_date_purchased
FROM student_engagement se
LEFT JOIN student_info si ON se.student_id = si.student_id
LEFT JOIN student_purchases sp ON se.student_id = sp.student_id
GROUP BY se.student_id
ORDER BY- first_date_purchased DESC
| student_id | date_registered | first_date_watched | first_date_purchased |
|---|---|---|---|
| 255201 | 2021-12-01 | 2021-12-02 | 2021-12-01 |
| 255203 | 2021-12-01 | 2021-12-01 | 2021-12-01 |
| 255204 | 2021-12-01 | 2021-12-01 | 2021-12-01 |
| 255200 | 2021-12-01 | 2021-12-01 | null |
| 255205 | 2021-12-01 | 2021-12-01 | null |
/*Posible solution but it seems odd to me you want the nulls*/
SELECT se.student_id,
si.date_registered,
MIN(se.date_watched) AS first_date_watched,
MIN(sp.date_purchased) AS first_date_purchased
FROM student_engagement se
LEFT JOIN student_info si ON se.student_id = si.student_id
LEFT JOIN student_purchases sp ON se.student_id = sp.student_id
GROUP BY se.student_id, si.date_registered
HAVING first_date_purchased >= first_date_watched
or first_date_purchased is null
ORDER BY first_date_purchased DESC
| student_id | date_registered | first_date_watched | first_date_purchased |
|---|---|---|---|
| 255203 | 2021-12-01 | 2021-12-01 | 2021-12-01 |
| 255204 | 2021-12-01 | 2021-12-01 | 2021-12-01 |
| 255200 | 2021-12-01 | 2021-12-01 | null |
| 255205 | 2021-12-01 | 2021-12-01 | null |
/*or's can be slow so maybe this will be faster...*/
SELECT se.student_id,
si.date_registered,
MIN(se.date_watched) AS first_date_watched,
MIN(sp.date_purchased) AS first_date_purchased
FROM student_engagement se
LEFT JOIN student_info si ON se.student_id = si.student_id
LEFT JOIN student_purchases sp ON se.student_id = sp.student_id
GROUP BY se.student_id, si.date_registered
HAVING
coalesce(first_date_purchased,first_date_watched) >= first_date_watched
ORDER BY first_date_purchased DESC
| student_id | date_registered | first_date_watched | first_date_purchased |
|---|---|---|---|
| 255203 | 2021-12-01 | 2021-12-01 | 2021-12-01 |
| 255204 | 2021-12-01 | 2021-12-01 | 2021-12-01 |
| 255200 | 2021-12-01 | 2021-12-01 | null |
| 255205 | 2021-12-01 | 2021-12-01 | null |