0

I have a scenario where each of my users buys two things from my shop, at different times.

+---------------------+----------+ | purchase_time | user_id | +---------------------+----------| | 2016-06-13 15:52:28 | 282725 | | 2016-05-15 15:35:04 | 282725 | | 2016-11-12 14:41:25 | 270307 | | 2016-10-17 15:01:64 | 270307 | | 2016-05-17 14:22:35 | 282085 | | 2016-02-11 14:38:31 | 282085 | +---------------------+----------+

What I'm trying to figure out is how long does it take (in terms of days/hours) between the initial purchase and the second purchase. I'm stumped on how to group each pair of purchases, do the subtraction, and then show it again on a per-user basis. Any advice?

Improve this question

1 Answer 1

Reset to default
1

If there are always exactly two purchases, then just use min() and max():

select user_id, timestampdiff(hour, min(purchase_time), max(purchase_time)) as diff_hours from t group by user_id;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.