CODEWITHSUNDEEP
mysql
Jan Richmond Padilla
Jan Richmond Padilla

Reputation: 59

Calculating the difference between the signup date and date of first purchase

So, I have two tables:

member_id        item             purchase_date
---------        ----             -------------

2326546        iron rod            2015-08-15
8410245        digital camera      2015-08-25 
5820364        earphones           2015-08-30
8410245        monopad             2015-09-02 
8410245        lens                2015-09-02 
8410245        cleaning kit        2015-09-02 
9451357        desk organizer      2015-09-13 
9451357        storage box         2015-09-13 
7635926        sticky pads         2015-10-05 
1988162        pen holder          2015-10-05



member_id      signup_date
---------      ----------- 
1988162        2015-08-01
8410245        2015-08-05
5820364        2015-08-05
2326546        2015-08-20
9451357        2015-08-22    
7635926        2015-08-30

I got the users transaction histories for those that signed-up in August.

What I want to see is the difference, in days, of the first purchase they made and the date of their signup.

Please help.

Upvotes: 0

Views: 77

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

Try this query:

SELECT t.member_id, DATEDIFF(t.first_purchase_date, s.signup_date)
FROM
(
    SELECT member_id, MIN(purchase_date) AS first_purchase_date
    FROM purchase
    GROUP BY member_id
) t INNER JOIN signup s ON t.member_id = s.member_id

SQLFiddle

You will note that one of the records in the query has a negative difference. This is because one of your users has his first purchase before his actual signup date.

Upvotes: 3

Related Questions