20260220 TIL

[Today I Learn]

- SQL codekata

- 머신러닝 세션

- QCC

---

[SQL codekata]

- 문제 1.

1. 문제 링크: https://www.hackerrank.com/challenges/earnings-of-employees/problem

2. 정답 코드

with ranked as ( 
    select 
        employee_id, 
        salary * months as earnings,
        rank() over (order by (salary * months) desc) as rnk
    from employee
)
select earnings, count(*) 
from ranked 
where rnk = 1

select e.salary * e.months as earnings, count(*) 
from employee e 
where e.salary * e.months = ( 
    select em.salary * em.months as earnings
    from employee em 
    order by em.salary * em.months desc 
    limit 1
)
group by earnings

select e.salary * e.months as earnings, count(*) 
from employee e 
group by e.salary * e.months
order by e.salary * e.months desc 
limit 1

select salary * months as earnings, count(*) 
from employee 
where (salary * months) = (
    select max(e.salary * e.months)
    from employee e
)
group by salary * months

select earnings, cnt
from ( 
    select 
        salary * months as earnings,
        count(*) as cnt,
        rank() over (order by (salary * months) desc) as rnk 
    from employee 
    group by salary * months
) sub
where rnk = 1

 

---

[ QCC 연습문제 ]

select distinct user_id
from emails 
where signup_date >= '2022-06-01' and signup_date < '2022-07-01'
order by user_id asc

select count(distinct email_id)
from texts t
where exists ( 
  select 1
  from texts t2
  where signup_action = 'N'  
  and t.email_id = t2.email_id
)

select count(distinct email_id)
from ( 
  select email_id 
  from texts 
  group by email_id 
  having sum(case when signup_action = 'N' then 1 else 0 end) > 0
) as sub