Find Latest Child Record (With Conditions) Per Parent Record

I have 2 tables with a parent/child hierarchy and am trying to pull the ID for the latest child record for each parent record, but also with conditions applied on the child record that is being pulled.

Here’s what my tables look like:

-----------
| Quizzes |
-----------
| ID      | 
-----------
| 1       |
| 2       |
| 3       |
-----------

-------------------------------------
| QuizAttempts                      |
-------------------------------------
| ID | QuizID | AttemptedAt         |
-------------------------------------
| 1  | 1      | 2021-01-01 05:00:00 |
| 2  | 1      | 2021-01-01 08:30:00 |
| 3  | 2      | 2021-01-01 05:00:00 |
| 4  | 3      | 2021-01-01 07:00:00 |
| 5  | 3      | 2021-02-01 07:00:00 |
| 6  | 3      | 2021-03-01 07:00:00 |
-------------------------------------

Now, let’s say I want to pull ID of latest QuizAttempt for each Quiz that was attempted from 2021-01-01 00:00:00 to 2021-01-01 23:59:59 to look something like this:

------------------------------------------------
| QuizID | QuizAttemptID | AttemptedAt         |
------------------------------------------------
| 1      | 2             | 2020-01-01 08:30:00 |
| 2      | 3             | 2020-01-01 05:00:00 |
| 3      | 4             | 2020-01-01 07:00:00 |
------------------------------------------------

Currently, I’m just doing a self-referential joins to compare IDs to pull the latest QC:

SELECT attempts1.ID
FROM QuizAttempts AS attempts1
LEFT OUTER JOIN QuizAttempts AS attempts2
ON (attempts1.QuizID = attempts2.QuizID
    AND attempts1.ID < attempts2.ID)
WHERE attempts2.ID IS NULL;

Which works for pulling the latest child record. But, when I add the date range conditions (like by adding attempts1.AttemptedAt BETWEEN '2021-01-01 00:00:00' AND '2021-01-01 23:59:59'), I get empty results.

Answer

I would suggest window functions in a subquery:

SELECT qa.*
FROM (SELECT qa.*,
             ROW_NUMBER() OVER (PARTITION BY QuizID ORDER BY id DESC) as seqnum
      FROM QuizAttempts qa
      WHERE qa.attemptedat >= '2021-01-01' AND
            qa.attemptedat < '2021-01-02'
     ) qa
WHERE seqnum = 1;

Note that the date arithmetic is much simpler without having to deal with the time components.

EDIT:

You can also use a correlated subquery:

SELECT qa.*
FROM QuizAttempts qa
WHERE qa.id = (SELECT MAX(qa2.id),
               FROM QuizAttempts qa2
               WHERE qa2.QuizID = qa.QuizID AND
                     qa2.attemptedat >= '2021-01-01' AND
                     qa2.attemptedat < '2021-01-02'
              ) qa;