with cte as(SELECT *, sum(case when type = 'log in' then 1 else 0 end)over(partition by id order by activity_time) as stat FROM UserActivity group by 1,2,3),v1 as( select id,stat, min(activity_time) as minimum,max(activity_time)as maximum from cte group by 1,2),v2 as( select *, timestampdiff(minute, minimum,maximum) as diff from v1),v3 as( select id,date(minimum) as dates, sum(diff) as hrs from v2 group by 1,2) select id, case when avg(hrs) >= 60 and avg(hrs) < 90 then 'Light User' when avg(hrs) >= 90 and avg(hrs) < 120 then 'Medium User' when avg(hrs) >= 120 then 'Heavy User' end as UserType from v3 group by 1;
No. My friend attended it. As per him, 1. He received a call from a person. He was asked some 8-10 basic questions on Sql. This is round 0 2. Two technical discussions 3. Last managerial round
WITH cte AS ( SELECT *, SUM(CASE WHEN type='log in' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY activity_time) AS flag FROM UserActivity ) , cte1 AS( SELECT id, DATE(activity_time) AS activity_date, TIMESTAMPDIFF(MINUTE, MIN(activity_time), MAX(activity_time)) AS minutes_per_session FROM cte GROUP BY id, flag, DATE(activity_time) ) SELECT id, CASE WHEN AVG(total_minutes_per_day)>=60 AND AVG(total_minutes_per_day)=90 AND AVG(total_minutes_per_day)=120 THEN 'Heavy User' END AS user_type FROM ( SELECT id, activity_date, SUM(minutes_per_session) AS total_minutes_per_day FROM cte1 GROUP BY id, activity_date) Z GROUP BY id;
Nice explanation bro. Thanks a lot
with cte as(SELECT *,
sum(case when type = 'log in' then 1 else 0 end)over(partition by id order by activity_time) as stat
FROM UserActivity
group by 1,2,3),v1 as(
select id,stat,
min(activity_time) as minimum,max(activity_time)as maximum
from cte
group by 1,2),v2 as(
select *, timestampdiff(minute, minimum,maximum) as diff
from v1),v3 as(
select id,date(minimum) as dates,
sum(diff) as hrs
from v2
group by 1,2)
select id,
case when avg(hrs) >= 60 and avg(hrs) < 90 then 'Light User'
when avg(hrs) >= 90 and avg(hrs) < 120 then 'Medium User'
when avg(hrs) >= 120 then 'Heavy User' end as UserType from v3
group by 1;
And how did you find the logic of this problem like 60 minutes to 90 minutes i mean how did you set the time limit
I think that is the question. Based on these time limits we need to categorize.
Bro in this question what was the purpose of scroll time?
Did you attend flipkart interview?? Can you explain about the interview process please
No. My friend attended it. As per him,
1. He received a call from a person. He was asked some 8-10 basic questions on Sql. This is round 0
2. Two technical discussions
3. Last managerial round
@@datasculptor2895 oh thanks sir
WITH cte AS (
SELECT *,
SUM(CASE WHEN type='log in' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY activity_time) AS flag
FROM UserActivity
)
, cte1 AS(
SELECT id, DATE(activity_time) AS activity_date,
TIMESTAMPDIFF(MINUTE, MIN(activity_time), MAX(activity_time)) AS minutes_per_session
FROM cte
GROUP BY id, flag, DATE(activity_time)
)
SELECT id,
CASE WHEN AVG(total_minutes_per_day)>=60 AND AVG(total_minutes_per_day)=90 AND AVG(total_minutes_per_day)=120 THEN 'Heavy User' END AS user_type
FROM (
SELECT id, activity_date,
SUM(minutes_per_session) AS total_minutes_per_day
FROM cte1
GROUP BY id, activity_date) Z
GROUP BY id;
not working brother