MySQL - join table on incrementing date

Question

I am trying to find when each participant dropped out of my study. I have tables studies, participants and measurements.

A participant may have only one measurement taken per day, but may not necessarily have a measurement taken every day.

In the end I'd like to have 2 columns: day, % remaining where day is the start of the study, and % remaining is the number of participants remaining in the study. The participants remaining is determined by a dropped_out_at datetime column on the participants table.

I want to return the result even if there were no measurements taken for a participant or even a study on that day.

I am trying to join a list of consecutive dates to my measurements table:

select `day`, 
(
count(distinct p.id) 
/
        (
            select count(*) 
            from participants 
            where study_id = 1
        )
        * 100
)

as `% remaining`
from 
(
    select @increment := date_add(@increment, interval 1 day) `day`
    from 
    (
        select m2.measured_at 
        from measurements m2
    ) as `inc`, `measurements`
) as `res`
left join `measurements` m on date(m.measured_at) = `day`
inner join `participants` p on participant_id = p.id
where p.dropped_out_at < m.measured_at
and study_id = 1
group by `day`
order by `day`;

This seems to run, but never gets any results. Am I on the right track?

MySQL - join table on incrementing date

0 Answers0