How can I fetch the first 7 days with scheduled lessons for a user in the future using a SQL query?

Question

I have a database table as follows:

id	lesson_start	lesson_end	instructor_id	student_id
1	2023-06-01 04:00:00.000000	2023-06-01 06:00:00.000000	3	4
2	2023-03-18 11:00:00.000000	2023-03-18 12:30:00.000000	3	4
...
...

I want to fetch the first 7 days in the future where there are lessons scheduled for a specific user, rather than simply adding 7 days to the current date. This means that if there are no lessons scheduled for a user on a particular day within the next 7 days, that day should not be included in the result set, but it should look one further.

Generally, there are multiple lessons planned on a single day for a user, so I want to fetch all those lessons for all those days.

Right now I'm using Java with Spring (with a PostgreSQL database, but I'm willing to switch if that can make the difference) and I'm trying to write the queries on my own by using @Query.

Is there a way how to do this?

I tried to use the built-in features of Spring JPA to get this to work, but to no avail. After that, I searched around and tried to write my own queries by using GROUP BYand LIMIT clauses, but that didn't give me the results I wanted.

It couldn't get it to "see" past the next 7 days, even if they were empty.

Answer 1

I want to fetch the first 7 days in the future where there are lessons scheduled for a specific user

An approach uses dense_rank():

select *
from (
    select t.*,
        dense_rank() over(partition by student_id order by lesson_start::date) rn
    from mytable t
    where lesson_start >= current_date + interval '1' day
) t
where rn <= 7
order by student_id, lesson_start

The idea is to assign a rank to each future student lesson, that only increments when the day changes. You can run the subquery first to display the logic.

This does the work for multiple users at once, but you can add a where clause to the subquery to filter on a specific id if you like (in that case, the partition by clause becomes unnecessary).