Posted by Sylvia, April 29, 2022, 11:53 p.m.
106 Mobile VS Web Time Spent Per User
I understand what the official solution is trying to accomplish; however, by using COALESCE(), my solution can also take care of situations when a user only browses on the web/mobile while obtaining the time spent on mobile vs web.
Will you please provide some guidance on why my solution does not yield the same result as the official solution?
My solution:
WITH time_mobile AS
(
SELECT a.user_id,
SUM(b.duration) as total_dur_mobile
FROM session_mobile a
INNER JOIN session_mobile_duration b
ON a.session_id = b.session_id
GROUP BY a.user_id
),
time_web AS
(
SELECT a.user_id,
SUM(b.duration) as total_dur_web
FROM session_web a
INNER JOIN session_web_duration b
ON a.session_id = b.session_id
GROUP BY a.user_id
)
SELECT COALESCE(a.user_id, b.user_id) as user_id,
COALESCE(a.total_dur_mobile, 0) *100.0/(COALESCE(a.total_dur_mobile, 0) + COALESCE(b.total_dur_web, 0)) as web_percentage,
COALESCE(b.total_dur_web, 0) *100.0/(COALESCE(a.total_dur_mobile, 0) + COALESCE(b.total_dur_web, 0)) as mobile_percentage
FROM time_mobile a
FULL OUTER JOIN time_web b
ON a.user_id = b.user_id
ORDER BY user_id