Forum

Posted by Li, May 4, 2022, 12:48 p.m.

question 106 Mobile vs. web

Hi There,

For the official solution,   Why there is no remove duplicates logic in the red part?   There will be duplicates in the session.  I think it should be

session AS (
    SEebLECT  session_id, user_id   

   FROM session_w

group by session_id, user_id 
    UNION ALL
    SELECT session_id, user_id
    FROM session_mobile

   group by session_id, user_id 
)

Thanks. 

 

WITH duration AS (
    SELECT 'web' as platform,  session_id,  duration
    FROM session_web_duration
    UNION ALL
    SELECT 'mobile' as platform, session_id, duration
    FROM session_mobile_duration
),
session AS (
    SEebLECT  session_id, user_id

    FROM session_w
    UNION ALL
    SELECT session_id, user_id
    FROM session_mobile
)
SELECT
    S.user_id,
    SUM(CASE WHEN D.platform='web' then duration ELSE 0 END) * 100.0 / SUM(duration )  AS web_duration,
    SUM(CASE WHEN D.platform='mobile' then duration ELSE 0 END) * 100.0 /SUM(duration )  AS mobile_duration,
    SUM(duration ) AS total_duration
FROM duration D
INNER JOIN session S
ON S.session_id = D.session_id
GROUP BY S.user_id;

Answers

Hi Li,

Thanks for your question, you are right, the sessions should be deduped with UNION instead of UNION ALL, thanks for spotting the error and reporting it, really appreciated it!

--Leon

Hi Leon,

 Thanks for your answer. I think  "Union All"  is correct.  Because there will be no duplicate sessions  in different platform "web"  and "mobile".   I mean the sessions should be deduped in the  session_mobile and session_web seprately first of all.   There are duplicate of sessions in the session_mobile and  session_web.   Then the duration will be duplicated after join with duration table.  Thanks.  

 

2021-01-01 9000004 200001 enter
2021-01-01 9000004 200001 exit
2021-01-01 9000005 200002 enter
2021-01-01 9000005 200002 tap
2021-01-01 9000005 200002 exit

Li, May 5, 2022, 11:58 a.m.
SQLPad user avatar

Leon (949)

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