How can I find days between different paired rows?
- by Anthony
I've been racking my brain about how to do this in one query without PHP code.
In a nutshell, I have a table that records email activity. For the sake of this example, here is the data:
recipient_id activity date
1 delivered 2011-08-30
1 open 2011-08-31
2 delivered 2011-08-30
3 delivered 2011-08-24
3 open 2011-08-30
3 open 2011-08-31
The goal: I want to display to users a single number that tells how many recipients open their email within 24 hours.
E.G. "Users that open their email within 24 hours: 13 Readers"
In the case of the sample data, above, the value would be "1". (Recipient one was delivered an email and opened it the next day. Recipient 2 never opened it and recipient 3 waited 5 days.)
Can anyone think of a way to express the goal in a single query?
Reminder: In order to count, the person must have a 'delivered' tag and at least one 'open' tag. Each 'open' tag only counts once per recipient.
