[내일배움캠프] TIL 48일차 26.04.10(금)

QCC 3회

 

1번

select login_count as unique_logins, count(1) employee_count
from (
	select employee_id, count(1) login_count
    	from logins
	where login_result = 'SUCCESS'
	and date_format(login_time, '%Y-%m') between '2023-07' and '2023-09'
	group by 1
) a
group by 1
order by 1

-- and login_time between '2023-07-01' and '2023-09-30'
-- and date_foramt(login_time, '%Y-%m-%d') between '2023-07-01' and '2023-09-30'
-- and login_time >= '2023-07-01' and login_time <= '2023-10-01'

with employee_logins as (
	select employee_id, count(1) login_count
    	from logins
    	where login_result = 'SUCCESS'
    	and date_format(login_time, '%Y-%m') between '2023-07' and '2023-09'
   	group by 1
)
select login_count as unique_logins, count(1) employee_count
from employee_logins
group by 1
order by 1

 

 

 

2 번

select *
from employee_salary
where salary = (
	select min(salary)
   	from (
    		select distinct salary
        	from employee_salary
        	order by salary desc
        	limit 3
   	 ) a
)

select *
from employee_salary
where salary = (
   select distinct salary
   from employee_salary
   order by salary desc
   limit 1 offset 2
)

with ranked_salary as (
	select 
		employee_id,
    		name,
		salary,
		-- rank() over (order by salary desc),
    		dense_rank() over (order by salary desc) rn,
    		-- row_number() over (order by salary desc)
	from employee_salary
)
select employee_id, name, salary
from ranked_salary
where rn = 3
order by employee_id

 

 

3번

select 
    -- m.sender_id, 
    -- es.department as sender_department,
    -- m.receiver_id,
    -- er.department as receiver_department,
    round(sum(case when es.department <> er.department then 1 aelse 0 end) / count(1) * 100, 1) as inter_department_msg_pct
from messages m
left join employees es
on m.sender_id = es.employee_id
left join employees er
on m.receiver_id = er.employee_id

 

 

4번

-- select distinct user_id
-- from ad_attribution aa
-- join user_sessions us
-- on aa.session_id = us.session_id
-- where converted = 1

select *
from (
	select us.user_id, aa.session_id, aa.channel, us.created_at,
		row_number() over (partition by us.user_id order by us.created_at) rn
		-- 유저별 세션별 방문시간 순위
	from ad_attribution aa
	join user_sessions us
	on aa.session_id = us.session_id
) a
where rn=1
and user_id in (
	select distinct user_id
    from ad_attribution aa
    join user_sessions us
    on aa.session_id = us.session_id
    where converted = 1
)
order by 1