I need to calculate the dwelling time per user per day of two modes of a mobile software and that's what I tried:
select mode, ROUND(AVG(dwell), 2) AS avg_dwell
from (
select mode, user_id, year(time) as year,
month(time) as month, day(time) as day,
SUM(unix_timestamp(action_end_time) - unix_timestamp(action_start_time) END) AS dwell
from table
group by mode, user_id, year(time),
month(time), day(time)
) tmp
group by mode;
But why it ends up like this:
mode avg_dwell
-----
mode1 12.23
mode2 24.34
mode1 43.50
mode2 454.56
mode1 34.70
mode2 352.10
...
Generally speaking, I expected to see there are only 2 rows, such as,
mode1 XXX
mode2 XXX
which mean it represents the average dwell time per user per day of 2 modes respectively. So it doesn't make sense that the query doesn't aggregate the users.
What did I miss and what's the way out?