I'm currently working on a statistic page and one of the table makes me struggle.

```
+----+-----------+----------+---------------------+
| id | id_status | id_queue | datetime            | // Comments
+----+-----------+----------+---------------------+
| 1  | 1         | 1        | 2021-07-01 17:03:13 | //<- last_entry: id_queue:1  for: #1#       (id_status: 1)
| 2  | 1         | 2        | 2021-07-01 17:03:18 | //<- last_entry: id_queue:2  for: #1#2#         (id_status: 1)
| 9  | 1         | 9        | 2021-07-01 17:03:45 |
| 10 | 1         | 10       | 2021-07-01 17:03:49 |
| 11 | 2         | 7        | 2021-07-01 17:04:10 |
| 12 | 3         | 7        | 2021-07-01 17:07:36 |
| 13 | 2         | 10       | 2021-07-01 17:07:54 |
| 14 | 3         | 10       | 2021-07-01 17:08:36 | //<- last_entry: id_queue:10 for: #1#       (id_status: 3)
| 15 | 2         | 9        | 2021-07-01 17:15:04 |
| 16 | 5         | 9        | 2021-07-01 17:15:24 | //<- last_entry: id_queue:9  for: #1#2#3#4# (id_status: 5)
| 18 | 3         | 7        | 2021-07-01 17:35:58 | //<- last_entry: id_queue:7  for: #1#       (id_status: 3)
//- -   -   -   - new day #2#  -    -   -   -   -   -   
| 19 | 2         | 7        | 2021-07-02 18:36:23 |
| 21 | 3         | 1        | 2021-07-02 18:39:49 |
| 22 | 14        | 1        | 2021-07-02 18:40:17 |
| 23 | 14        | 10       | 2021-07-02 18:40:17 |
| 24 | 2         | 1        | 2021-07-02 19:14:21 |
| 25 | 1         | 1        | 2021-07-02 19:14:32 | //<- last_entry: id_queue:1  for: #2#3#4#   (id_status: 1)
| 26 | 2         | 10       | 2021-07-02 19:14:35 |
| 27 | 1         | 10       | 2021-07-02 19:14:39 | //<- last_entry: id_queue:10 for: #2#3#4#   (id_status: 1)
| 28 | 1         | 7        | 2021-07-02 19:14:46 | //<- last_entry: id_queue:7  for: #2#3#4#   (id_status: 1)
//- -   -   -   - new day #3#  -    -   -   -   -   -   
| 31 | 2         | 2        | 2021-07-05 17:20:39 |
| 32 | 3         | 2        | 2021-07-05 17:24:59 | //<- last_entry: id_queue:2  for: #3#       (id_status: 3)
//- -   -   -   - new day #4#  -    -   -   -   -   -   
| 33 | 2         | 3        | 2021-07-06 09:38:03 |
| 34 | 3         | 3        | 2021-07-06 09:38:16 | //<- last_entry: id_queue:3  for: #4#       (id_status: 3)
| 35 | 2         | 6        | 2021-07-06 10:12:18 | //<- last_entry: id_queue:6  for: #4#       (id_status: 2)
| 37 | 2         | 2        | 2021-07-06 11:37:50 |
| 38 | 13        | 2        | 2021-07-06 12:02:19 |
| 39 | 2         | 2        | 2021-07-06 12:02:21 |
| 40 | 13        | 2        | 2021-07-06 12:04:12 | //<- last_entry: id_queue:2  for: #4#       (id_status: 13)
+----+-----------+----------+---------------------+

```

I'm looking to get for each `%Y/%m/%d` the COUNT of each last entry until the current date of id\_status from each id\_queue.

Basically, for every day, it makes the count of the last entries based on the actual `datetime` column.

An example of the output based on the previous table above:

```
+-----------+--------------+------------+-------------------------------------------+
| id_status |  occurrences |  day       |  HELP_COLUMN(comment id from table above) |
+-----------+--------------+------------+-------------------------------------------+
| 1         | 2            | 2021-07-01 |  #1#                                      |
| 3         | 2            | 2021-07-01 |  #1#                                      |
| 5         | 1            | 2021-07-01 |  #1#                                      |
| 2         | 4            | 2021-07-02 |  #2#                                      |
| 5         | 1            | 2021-07-02 |  #2#                                      |
| 1         | 3            | 2021-07-05 |  #3#                                      |
| 3         | 1            | 2021-07-05 |  #3#                                      |
| 5         | 1            | 2021-07-05 |  #4#                                      |
| 1         | 3            | 2021-07-05 |  #4#                                      |
| 3         | 1            | 2021-07-05 |  #4#                                      |
| 2         | 1            | 2021-07-05 |  #4#                                      |
| 13        | 1            | 2021-07-05 |  #4#                                      |
+-----------+--------------+------------+-------------------------------------------+

```

* For #1# (first day) take last entries of id\_queue (based on
max(datetime).
* For #2# (second day) take the last entries of id\_queue
(base on max(datetime)) (as you can see some of the last entries of
id\_queue are in the previous day). etc...

I tried multiple ways but I start to feel that this looks like it needs to be solved with an iterator... but I can't execute one SQL query by day this would cost too much performance.

Does someone know which SQL request I could use?
Tanks.

---

**Edit:** Here is another example:

**Input:**

```
+-----+-----------+----------+---------------------+
| id  | id_status | id_queue |      datetime       |
+-----+-----------+----------+---------------------+
|  61 |         5 |        1 | 2021-07-01 15:03:40 |
| 132 |         5 |        1 | 2021-07-01 16:39:13 |
|   1 |         1 |        1 | 2021-07-01 17:03:13 | <- last 1 :    1 #1#
|   2 |         1 |        2 | 2021-07-01 17:03:18 | <- last 2 :    1 #1#2#
|   3 |         1 |        3 | 2021-07-01 17:03:21 | <- last 3 :    1 #1#2#
|   4 |         1 |        4 | 2021-07-01 17:03:25 | <- last 4 :    1 #1#2#3#
|   5 |         1 |        5 | 2021-07-01 17:03:29 | <- last 5 :    1 #1#2#3#
|   6 |         1 |        6 | 2021-07-01 17:03:33 | <- last 6 :    1 #1#2#3#
|   7 |         1 |        7 | 2021-07-01 17:03:37 | 
|   8 |         1 |        8 | 2021-07-01 17:03:41 | <- last 8 :    1 #1#2#3#
|   9 |         1 |        9 | 2021-07-01 17:03:45 | 
|  10 |         1 |       10 | 2021-07-01 17:03:49 |
|  11 |         2 |        7 | 2021-07-01 17:04:10 |
|  12 |         3 |        7 | 2021-07-01 17:07:36 |
|  13 |         2 |       10 | 2021-07-01 17:07:54 |
|  14 |         3 |       10 | 2021-07-01 17:08:36 | <- last 10 :   3 #1#
|  15 |         2 |        9 | 2021-07-01 17:15:04 |
|  16 |         5 |        9 | 2021-07-01 17:15:24 | <- last 9 :    5 #1#2#3#
|  17 |         2 |        7 | 2021-07-01 17:35:36 |
|  18 |         3 |        7 | 2021-07-01 17:35:58 | <- last 7 :    3 #1#
|  19 |         2 |        7 | 2021-07-02 18:36:23 |
|  20 |         2 |        1 | 2021-07-02 18:36:39 |
|  21 |         3 |        1 | 2021-07-02 18:39:49 |
|  23 |        14 |       10 | 2021-07-02 18:40:17 |
|  22 |        14 |        1 | 2021-07-02 18:40:17 |
|  24 |         2 |        1 | 2021-07-02 19:14:21 |
|  25 |         1 |        1 | 2021-07-02 19:14:32 | <-- last 1 :   1 #2#3#
|  26 |         2 |       10 | 2021-07-02 19:14:35 |
|  27 |         1 |       10 | 2021-07-02 19:14:39 | <-- last 10 :  1 #2#3#
|  28 |         1 |        7 | 2021-07-02 19:14:46 | <-- last 7 :   1 #2#3#
|  29 |         2 |        3 | 2021-07-05 15:26:27 |
|  30 |         3 |        3 | 2021-07-05 15:26:48 | <--- last 3 :  3 #3#
|  31 |         2 |        2 | 2021-07-05 17:20:39 |
|  32 |         3 |        2 | 2021-07-05 17:24:59 | <--- last 2 :  3 #3#
+-----+-----------+----------+---------------------+

```

**#1 (2021-07-01):**

* 1,1,1,1,1,1,1 (7 occurences)
* 3,3 (2 occurences)
* 5 (1 occurences)

**#2 (2021-07-02):** : <https://i.ibb.co/vDhL05q/sublime-text-or-MEzs-GFh-Q.jpg>

* 1,1,1,1,1,1,1,1,1 (9 occurences)
* 5 (1 occurrences)

**#3 (2021-07-05):**

* 1,1,1,1,1,1,1 (7 occurrences)
* 3,3 (2 occurrences)
* 5 (1 occurences)

**Output:**

```
+-----------+-------------+------------+
| id_status |  occurences |     day    |
+-----------+-------------+------------+
|         1 |           7 | 2021-07-01 |
|         3 |           2 | 2021-07-01 |
|         5 |           1 | 2021-07-01 |
|         1 |           9 | 2021-07-02 |
|         5 |           1 | 2021-07-02 |
|         1 |           7 | 2021-07-05 |
|         3 |           2 | 2021-07-05 |
|         5 |           1 | 2021-07-05 |
+-----------+-------------+------------+

```