Retrieve only last record of each group between range

My table is

+-------+----------------+---------+--------------+-------------------+
|  id   | invoice_number | user_id | created_date | next_contact_date |
+-------+----------------+---------+--------------+-------------------+
| 42699 |            100 |     236 | 2021-05-19   | 2022-08-09        |
| 42693 |            100 |     236 | 2021-03-01   | 2022-08-09        |
| 42694 |            100 |      30 | 2021-03-01   | 2022-08-08        |
| 42695 |            100 |     235 | 2021-03-03   | 2022-08-09        |
| 42696 |            100 |      30 | 2021-05-01   | 2022-08-02        |
| 42697 |            100 |     235 | 2021-05-03   | 2022-08-04        |
| 42698 |            100 |     236 | 2021-05-04   | 2022-08-02        |
| 42700 |            100 |      30 | 2021-05-19   | 2022-08-08        |
| 42701 |            100 |     235 | 2021-05-20   | 2022-08-08        |
| 42702 |            100 |     236 | 2021-05-19   | 2022-08-09        |
+-------+----------------+---------+--------------+-------------------+

I want to retrieve the last record based on id of each user_id group but only if it is between next_contact_date range

Desired Output

if user choose to retrieve records between 2022-08-01 and 2022-08-15 it should be

+-------+----------------+---------+--------------+-------------------+
|  id   | invoice_number | user_id | created_date | next_contact_date |
+-------+----------------+---------+--------------+-------------------+
| 42700 |            100 |      30 | 2021-05-19   | 2022-08-08        |
| 42701 |            100 |     235 | 2021-05-20   | 2022-08-08        |
| 42702 |            100 |     236 | 2021-05-19   | 2022-08-09        |
+-------+----------------+---------+--------------+-------------------+

That works fine !!!!

if now user choose to retrieve records between 2022-08-01 and 2022-08-05 it should be EMPTY because last id of each user_id group are not inside the next_contact_date.

What i have tried

SELECT * FROM mytable WHERE id IN 
                    (SELECT MAX(id) AS id FROM mytable where mytable.next_contact_date BETWEEN '2022-08-01' AND '2022-08-05 23:59:59.999' GROUP BY user_id)

i get the result below which is not what i want.The result should be empty since last records does not belong to the user-specified next_contact_date range.

+-------+----------------+---------+-----------------+-------------------+
|  id   | invoice_number | user_id |    created_date | next_contact_date |
+-------+----------------+---------+-----------------+-------------------+
| 42696 |            100 |      30 | 2021-05-01      | 2022-08-02        |
| 42697 |            100 |     235 | 2021-05-03      | 2022-08-04        |
| 42698 |            100 |     236 | 2021-05-04      | 2022-08-02        |
+-------+----------------+---------+-----------------+-------------------+

Solution

You need a further subquery to catch all user_id, that are in that time frame selected.

and use the user_ids to get the last ids, but sele3cts only those rows that are in the timeframe

CREATE TABLE mytable
    (`id` int, `invoice_number` int, `user_id` int, `created_date` date, `next_contact_date` date)
;
    
INSERT INTO mytable
    (`id`, `invoice_number`, `user_id`, `created_date`, `next_contact_date`)
VALUES
    (42699, 100, 236, '2021-05-19', '2022-08-09'),
    (42693, 100, 236, '2021-03-01', '2022-08-09'),
    (42694, 100, 30, '2021-03-01', '2022-08-08'),
    (42695, 100, 235, '2021-03-03', '2022-08-09'),
    (42696, 100, 30, '2021-05-01', '2022-08-02'),
    (42697, 100, 235, '2021-05-03', '2022-08-04'),
    (42698, 100, 236, '2021-05-04', '2022-08-02'),
    (42700, 100, 30, '2021-05-19', '2022-08-08'),
    (42701, 100, 235, '2021-05-20', '2022-08-08'),
    (42702, 100, 236, '2021-05-19', '2022-08-09')
;

SELECT * FROM mytable WHERE id IN 
                    (SELECT MAX(id) AS id FROM mytable 
                    where `user_id` IN 
                    (SELECT `user_id` FROM mytable WHERE mytable.next_contact_date BETWEEN '2022-08-01' AND '2022-08-05 23:59:59.999')
                    GROUP BY user_id)
AND mytable.next_contact_date BETWEEN '2022-08-01' AND '2022-08-05 23:59:59.999'

id | invoice_number | user_id | created_date | next_contact_date
-: | -------------: | ------: | :----------- | :----------------

SELECT * FROM mytable WHERE id IN 
                    (SELECT MAX(id) AS id FROM mytable 
                    where `user_id` IN 
                    (SELECT `user_id` FROM mytable 
                    WHERE mytable.next_contact_date BETWEEN '2022-08-01' AND '2022-08-15 23:59:59.999')
                    GROUP BY user_id)
AND mytable.next_contact_date BETWEEN '2022-08-01' AND '2022-08-15 23:59:59.999'

   id | invoice_number | user_id | created_date | next_contact_date
----: | -------------: | ------: | :----------- | :----------------
42700 |            100 |      30 | 2021-05-19   | 2022-08-08       
42701 |            100 |     235 | 2021-05-20   | 2022-08-08       
42702 |            100 |     236 | 2021-05-19   | 2022-08-09

db<>fiddle here