How can I find days between different paired rows?

Posted by Anthony on Stack Overflow See other posts from Stack Overflow or by Anthony
Published on 2012-09-01T02:39:40Z Indexed on 2012/09/01 3:38 UTC
Read the original article Hit count: 158

Filed under:
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about query