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;