MySql query how to get first where date count is less than

Goal

So my goal here is to have an sql query that returns the first date where there is no more than 5 records that respects the where clause(userId).

Current schema

|id|user_id|scheduled_at|
|1 |1      |2023-03-16  |
|2 |1      |2023-03-15  |
|3 |3      |2023-03-15  |
|4 |1      |2023-03-15  |
|5 |1      |2023-03-15  |

What have I achieved

    count(*) as cnt,
    scheduled_at
    from estudojo.user_scheduled_questions as uq where uq.user_id='1' and scheduled_at >=  '2023-03-14'
    group by scheduled_at
    having cnt < 5
    order by cnt ASC limit 1

The return is:

┌─────────┬─────┬─────────────────────────────┐
│ (index) │ cnt │        scheduled_at         │
├─────────┼─────┼─────────────────────────────┤
│    0    │  3  │ '2023-03-15'                │
└─────────┴─────┴─────────────────────────────┘

But still, not what I wanted, since the first date available is 2023-03-14

Expect

I expected something like:

┌─────────┬─────┬─────────────────────────────┐
│ (index) │ cnt │        scheduled_at         │
├─────────┼─────┼─────────────────────────────┤
│    0    │  0  │ '2023-03-14'                │
└─────────┴─────┴─────────────────────────────┘

>Solution :

Your data has now row for this user on March 14th, so the query cannot return what you expect.

If you want the query to check over a fixed date range, then you would need to generate the range first, and then bring the table with a left join. In MySQL 8.0, we can do this with a recursive query:

with recursive all_dates as (
    select '2023-03-14' scheduled_at
    union all 
    select scheduled_at + interval 1 day from all_dates where scheduled_at < current_date
)
select ad.scheduled_at, count(uq.user_id) cnt
from all_dates as ad
left join estudojo.user_scheduled_questions as uq
    on uq.user_id = 1 and uq.scheduled_at = ad.scheduled_at
group by ad.scheduled_at
having count(uq.user_id) < 5

The recursive CTE generates all dates between March 14th and today (you might want to adjust the bounds) ; then, the outer query counts how many rows can be found in the source table for each date (and for a given user) – the rest is just aggregation and filtering with having (which you already had in your original query).

Note: if the dates in your table have a time portion, we would need to change the join condition to inequalities :

...
left join estudojo.user_scheduled_questions as uq
    on uq.user_id = 1 
    and uq.scheduled_at >= ad.scheduled_at
    and uq.scheduled_at <  ad.scheduled_at + interval 1 day

Leave a Reply