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

3.21

1) Sales Person

select name
from SalesPerson 
where sales_id not in (
    select o.sales_id
    from orders as o
    join Company as c on c.com_id = o.com_id
    where c.name = 'RED'
)

2) Triangle Judgement

select *,
    case when x+y > z and y+z>x and z+x>y then 'Yes'
         else 'No'
    end as triangle 
from triangle

3) Biggest Single Number

select max(num) as num
from (
    select num
    from MyNumbers
    group by num
    having count(*) < 2
) as bsn

• from 서브쿼리 하지 않고 그냥 group by/having/order by로 조건 걸어주면 '숫자가 하나만 있는 경우'가 없을 경우 null값을 반환하지 못하는 문제 발생.

3.22

1) Not Boring Movies

select *
from cinema
where id % 2 = 1 and description != 'boring'
order by rating desc 

2) Swap Sex of Employees

update salary
set sex = case when sex = 'm' then 'f'
          else 'm'
          end 

3) Actors and Directors Who Cooperated At Least Three times

select actor_id,
       director_id
from ActorDirector
group by actor_id, director_id
having count(*) >= 3

3.23

1) Product Sales Analysis I

select p.product_name,
       s.year,
       s.price
from Sales as s
join Product as p on s.product_id = p.product_id 

2) Project Employees I

select p.project_id,
       round(avg(e.experience_years),2) as average_years
from Employee as e
join Project as p on e.employee_id = p.employee_id
group by p.project_id

3) Sales Analysis III

select p.product_id,
       p.product_name
from product as p
right join sales as s on p.product_id = s.product_id
group by product_id
having min(sale_date) >= '2019-01-01' and max(sale_date) <= '2019-03-31'

3.24

1) User Activity for the Past 30 Days I

select activity_date as day, count(distinct user_id) as active_users
from activity
group by activity_date
having activity_date > date_sub('2019-07-27', interval 30 day) 
and activity_date <= '2019-07-27'

2) Article Views I

select distinct(author_id) as id
from Views
where author_id = viewer_id
order by author_id;

3) Reformat Department Table

SELECT id,
        SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) as Jan_Revenue,
        SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) as Feb_Revenue,
        SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) as Mar_Revenue,
        SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) as Apr_Revenue,
        SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) as May_Revenue,
        SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) as Jun_Revenue,
        SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) as Jul_Revenue,
        SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) as Aug_Revenue,
        SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) as Sep_Revenue,
        SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) as Oct_Revenue,
        SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) as Nov_Revenue,
        SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) as Dec_Revenue           
FROM Department
GROUP BY id

3.25

1) Queries Quality and Percentage

select query_name,
      round(sum(rating/position)/count(*),2) as quality,
      round(sum(case when rating<3 then 1 else 0 end)/count(*)*100,2) 
      as poor_query_percentage
from Queries
group by query_name

2) Average Selling Price

select p.product_id,
    ifnull(round(sum(price * units) / sum(units), 2), 0) as average_price
from Prices p
    left join UnitsSold u on p.product_id = u.product_id 
        and u.purchase_date between p.start_date and p.end_date
group by p.product_id

3) Students and Examinations

select s.Student_id, 
	   s.Student_name, 
       u.subject_name, 
       count(e.subject_name) as attended_exams 
from Students AS s 
join Subjects AS u 
left join Examinations as e 
  on s.student_id = e.student_id and u.subject_name = e.subject_name
group by s.student_id, u.subject_name 
order by s.student_id, u.subject_name

3.26

1) List the products ordeedr in a period

select a.product_name , b.unit
from Products AS a
join 
(
    select product_id , sum(unit) AS unit
    from Orders
    where order_date between convert(DATE ,'20200201') AND convert(DATE, '20200229')
    group by product_id 
    having sum(unit) >= 100
) as b
on a.product_id = b.product_id

2) Replace Employee ID with the unique identifier

select e2.unique_id,
       e.name
from Employees e 
left join EmployeeUNI e2 on e.id = e2.id

3) Top Travllers

select u.name, 
	  ifnull(sum(r.distance), 0) as travelled_distance 
from Users as u left join rides as r on u.id = u.user_id
group by r.user_id
order by 2 desc, 1 asc