Sunil B N - 1 year ago 66
SQL Question

# Calculate Average Time between columns of two different table

I have two tables

`event`
and
`trans`
.

``````CREATE TABLE `event` (
`event_name` varchar(40) DEFAULT NULL,
`user_id` varchar(40) DEFAULT NULL,
`time` timestamp
);

CREATE TABLE `trans` (
`item_id` varchar(40) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`price` decimal(10,0) DEFAULT NULL,
`user_id` varchar(40) DEFAULT NULL,
`time` timestamp
)
``````

Assume Values for event table(multiple user visits):

``````|visit |a1 |2016-09-14 22:48:14 |
|visit |a2 |2016-09-14 22:48:28 |
|visit |a3 |2016-09-14 22:48:45 |
|visit |a1 |2016-09-15 15:10:39 |
|visit |a2 |2016-09-15 15:11:08 |
|visit |a1 |2016-09-15 15:12:34 |
``````

and for trans table

``````|i1 |1 |100 |a1 |2016-09-15 15:12:22 |
|i2 |2 |100 |a2 |2016-09-15 15:13:17 |
|j1 |1 |140 |a1 |2016-09-15 16:12:22 |
|j4 |3 |150 |a3 |2016-09-15 16:13:17 |
``````

I am trying to find average time between first visit and first purchase across users.

``````SELECT trans.user_id , event.time, trans.time as TransTime, AVG(TIMESTAMPDIFF(second,event.time,trans.time))
FROM event JOIN trans
ON trans.user_id = event.user_id and event.time < trans.time
GROUP BY trans.user_id;
``````

I am feeling like I am missing something here. Will this always pick first visit and first transaction?
And also how do I get first visit and purchase?

I would suggest aggregating the tables before joining them:

``````select avg(timestampdiff(second, e.mintime, t.mintime))
from (select user_id, min(time) as mintime
from event e
group by user_id
) e join
(select user_id, min(time) as mintime
from trans t
group by user_id
) t
on e.mintime = t.mintime;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download