SQL 코테 준비 (4월 2주)(리트코드)

4.3

1) Employees with missing information

select employee_id
from (
    select e.employee_id, e.name, s.salary
    from employees as e
    left join salaries as s on e.employee_id = s.employee_id

    union all

    select s.employee_id, e.name, s.salary
    from employees as e
    right join salaries as s on e.employee_id = s.employee_id
) as t
where name is null or salary is null
order by employee_id

2) Employees whose manager left the company

select employee_id
from employees
where manager_id is not null
and manager_id not in (select employee_id from employees) 
and salary < 30000
order by employee_id

3) Number of unique subjects taught by each teacher

select teacher_id,
       count(*) as cnt
from (
    select teacher_id, subject_id
    from teacher
    group by teacher_id, subject_id
)as t
group by teacher_id

4.4

1) Find Valid Emails

select user_id,
       email
from users
where regexp_like(email, '^[A-Za-z0-9_]+@[A-Za-z]+\\.com$')
order by user_id asc

2) Find Books with no available copies

SELECT 
    b.book_id, 
    b.title, 
    b.author, 
    b.genre,             
    b.publication_year,  
    br.borrow_count AS current_borrowers
FROM library_books b
JOIN (
    SELECT book_id, COUNT(*) AS borrow_count
    FROM borrowing_records
    WHERE return_date IS NULL
    GROUP BY book_id
) br ON b.book_id = br.book_id
WHERE b.total_copies = br.borrow_count
ORDER BY current_borrowers DESC, b.title ASC;

3) Find users with High token usage

SELECT
    user_id,
    COUNT(*) AS prompt_count,
    ROUND(AVG(tokens), 2) AS avg_tokens
FROM prompts
GROUP BY user_id
HAVING prompt_count >= 3 AND MAX(tokens) > avg_tokens
ORDER BY avg_tokens DESC, user_id;

4.5

1) Second Highest Salary

select max(salary) as SecondHighestSalary
from employee
where salary < (select max(salary) from employee)

2) Nth Highest Salary (medium)

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set n = n-1;
    RETURN (
        select (
            select distinct salary
            from employee
            order by salary desc
            limit 1 offset n
        )
  );
END

• offset을 사용하면 원하는 행의 수만큼 건너뛰고 그 이후의 행부터 검색이 가능하다.

4.6

1) Rank scores

select score,
       DENSE_RANK() over (order by score desc) as 'rank'
from Scores
order by score desc

2) Consecutive Numbers

select distinct num as ConsecutiveNums
from (
    select num,
           lead(num, 1) over (order by id) as lead1_num,
           lead(num, 2) over (order by id) as lead2_num
    from logs
) as t
where num = lead1_num
and num = lead2_num

4.7

1) Department Highest salary

with mbd as (
    select e.departmentID
         , d.name
         , MAX(salary) as max_salary
    from Employee as e
        inner join Department as d on e.departmentID = d.id
    group by e.departmentID, d.name
)

select mbd.name as Department
     , e.name AS Employee
     , e.salary as Salary
from mbd
    inner join Employee as e on mbd.departmentID = e.departmentID AND mbd.max_salary = e.salary

4.8

1) Game play analysis IV

with ac as (
    select player_id,
           datediff(event_date, min(event_date) over (partition by player_id)) = 1 as cc
    from activity
)

select round(sum(cc) / count(distinct player_id),2)  as fraction
from ac