select round(count(distinct if(datediff(next_event_date, event_date) <= 1,player_id,null)) / (select count(distinct player_id) from Activity),2) as fraction
from (
select *,
lead(event_date,1) over(partition by player_id order by event_date) as next_event_date
from Activity
) sub;
SQL
복사
→ 오답.. 왜 틀렸을까..
틀린 이유 : 문제의 의미를 잘 이해하지 못함
핵심 솔루션은 맨 처음 로그인한 이후의 재방문을 했는지 안했는지를 보는 것이라는 점을 유의해야 한다!!!
따라서, 모든 event_date 와 그 다음 방문 event_date 의 차이가 1이 되는 것을 찾는 것이 아니라 반드시 첫번째 방문 이후의 재방문의 날짜 차이가 1이 되는 것을 찾아줘야 한다.
SELECT ROUND(COUNT(t2.player_id)/COUNT(t1.player_id),2) AS fraction
FROM
(SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id) t1 LEFT JOIN Activity t2
ON t1.player_id = t2.player_id AND t1.first_login = t2.event_date - 1
SQL
복사
select distinct name
from Employee
where id in (
select managerId
from Employee
group by managerId
having count(*) >= 5
)
SQL
복사
SELECT ROUND(SUM(TIV_2016),2) AS TIV_2016 FROM Insurance a
WHERE EXISTS (SELECT * FROM Insurance WHERE PID <> a.PID AND TIV_2015 = a.TIV_2015) -- pid 가 다르고 tiv_2015는 같으면서
AND NOT EXISTS (SELECT * FROM Insurance WHERE PID <> a.PID AND (LAT,LON) = (a.LAT,a.LON)); -- pid가 다르지만 lat, lon이 같은 행은 제외하면서
SQL
복사
select id
,count(*) num
from
(select requester_id as id
from RequestAccepted
union all
select accepter_id as id
from RequestAccepted
) as a
group by id
order by num desc
limit 1;
SQL
복사