Check if the value has been the same for x consecutive days

Admin 1
Viewed 41

My goal is to get a list of users(uploaders) which have uploaded 0 byte files for 4 consecutive days.

My current query:

SELECT * FROM file_uploads
JOIN (
    SELECT max(date_added) as latest_date
    FROM file_uploads
    WHERE size = 0
    GROUP BY uploader_id
) as list

So in the JOIN I try to get the latest date grouped by uploader where a file is uploaded with the size of 0.

Now I need to check if the size has been 0 for -4 consecutive days since the latest_date, but that is what I can't figure out. Can I check it with a WHERE statement or do I need to do an IF statement or something else.

This is an example of the table I'm working with: https://ibb.co/Sf3RFSk

mariadb sql
1 Answers

If there is one row per date, you can filter by the maximum date and then count the number of rows that are 0 for each uploader_id:

select uploader_id
from file_uploads fu
where fu.date_uploaded >= (select max(fu2.date_uploaded) - interval 3 day
                           from file_uploads fu2
                           where fu2.uploader_id = fu.uploader_id
                          ) and
      fu.size = 0
group by uploader_id
having count(*) = 4;
user_11440350
Related