I could really use some help understanding how to structure my query.
Alright, in our system we have 'applicants'. We have 5 stages that we move our applicants through. each time an applicant moves into a new stage we insert a new row in a table called applicant_tracking with the applicant_id, status_id, and date. An example of the table is this:
I would like to make a query that would give me the average time it takes to move the applicants from one stage to the next. I dont mind having to do a separate query for each stage movement but I cant think of how to even do it for just one stage change.
The where conditions should have a.applicant_id = b.applicant_id AND a.status_id - b.status_id = 1 and a.date > b.date
the actual select part should select the difference in dates between the two. then you can just take the average of that.
note, i can't give any actual code as it depends on what database you use.