Forum

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

Answers

Hey Sylvia,

The session_mobile table has multiple events associated with the same session, so your sum (duration) is inflated with many duplicated records.

You will have to switch 

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
  ), 

to something like 

WITH time_mobile AS
(
  SELECT a.user_id, 
           SUM(b.duration) as total_dur_mobile
FROM (SELECT DISTINCT user_id, session_id FROM session_mobile) a
  INNER JOIN session_mobile_duration b
  ON a.session_id = b.session_id
  GROUP BY a.user_id
  ), 

 

And there is also a bug on the web/mobile column alias

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

-- Leon

SQLPad user avatar

Leon (949)

May 5, 2022, 9:55 a.m.