₍ᐢ•༝•ᐢ₎

문제

DEVELOPERS 테이블에서 Front End 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요. 결과는 ID를 기준으로 오름차순 정렬해 주세요.

풀이

비트 연산자를 활용해 해결 가능하다.

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = "Front End")
-- skillcodes 테이블에서 front end 카테고리에 해당하는 code의 합계와 developers 테이블의 skill_code 값을 &비트연산자로 계산
ORDER BY ID

결과값

계산 과정

WHERE SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = "Front End")

WHERE절을 살펴보자.

SKILLCODES 테이블의 Front End에 해당하는 모든 code 값을 합산하면 10256(10100000010000)이 도출된다.

각 개발자의 code를 이진수로 변환해보자.

• Carsen의 code는 2048 > 100000000000
• Cade의 code는 8452 > 10000100000100
• Jerami의 code는 400 > 110010000

각각의 값을 10256의 이진수 값 &연산해준다.

Carsen

10100000010000

00100000000000

-----------------------

00100000000000 > 2048로, React를 다룰 줄 아는 Frontend 개발자이다.

Cade

10100000010000

10000100000100

-----------------------

10000000000000 > 8192로, Vue를 다룰 줄 아는 Frontend 개발자이다.

Jerami

10100000010000

00000110010000

-----------------------

00000000010000 > 16로, JavaScript를 다룰 줄 아는 Frontend 개발자이다.

가끔 코딩테스트를 보면 비트연산자를 활용한 문제 풀이가 나오기도 한다. 이해하면 금방 적용할 수 있는 쉬운 내용이니 복습 한 번 더 하고 정리해야겠다.

문제

서울숲 일별 평균 대기오염도 데이터셋은 2022년 서울숲 대기오염도 측정소에서 매일 기록한 대기오염 정보를 담고 있습니다.
measurements 테이블의 pm10 컬럼에는 다양한 대기오염도 측정 기준 중에서도 미세먼지(PM10) 농도가 기록되어 있습니다. 이 데이터를 이용하여 당일의 미세먼지 농도보다 바로 다음날의 미세먼지 농도가 더 안좋은 날을 찾아주세요. 결과는 아래 컬럼들을 포함해야 합니다.

* 윈도우 함수 사용하기

* lead는 다음값, lag는 이전값을 가져오는 함수

풀이

with t1 as (select measured_at as today
    , lead(measured_at) over (order by measured_at) as next_day
    , pm10
    , lead(pm10) over (order by measured_at) as next_pm10
from measurements)

select today, next_day, pm10, next_pm10
from t1
where pm10 < next_pm10
-- 당일 미세먼지 농도보다 다음날의 미세먼지 농도가 더 안 좋은 날

product 테이블에 있는 product key를 모두 구입한 customer 테이블의 customer_id를 구하는 문제

having절과 count 함수를 함께 사용해서 조건절을 만들었다.

select distinct customer_id
from customer
group by customer_id
having count(distinct product_key) = (select count(distinct product_key) from product)

select firstName, lastName, city, state
from person p
left join address a on p.personId = a.personId

178. Rank Scores

select score, (dense_rank() over (order by score desc)) as 'rank'
from scores
order by score desc

184. Department Highest Salary

select d.name as Department, e.name as Employee, salary as Salary
from Employee e, Department d
where e.departmentId = d.id and (e.departmentId, salary) in 
(
    select departmentId, max(salary) from Employee 
    group by DepartmentId
)

풀이

select a1.machine_id, 
round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
from Activity a1
join Activity a2 on a1.process_id = a2.process_id and a1.machine_id = a2.machine_id
where a1.activity_type = 'start' and a2.activity_type = 'end'
group by a1.machine_id
order by a1.machine_id

* union all 활용해서 두 테이블을 합친 with절 테이블을 생성

with tbn as 
(
   select requester_id as id from RequestAccepted
   union all
   select accepter_id as id from RequestAccepted
)

select id, count(*) as num from tbn
group by id
order by num desc
limit 1

문제

Write a solution to find the ids of products that are both low fat and recyclable.

Return the result table in any order.

The result format is in the following example.

풀이

select p1.product_id 
from Products p1
inner join Products p2 on p1.product_id = p2.product_id
where p1.low_fats = 'Y' and p2.recyclable = 'Y'

문제

PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.

* 가격대 구간을 구하는 게 핵심

* case when을 활용해서 10,000 미만은 0, 나머지는 truncate 함수 사용해서 자르기

풀이

select (case when price < 10000 then 0
       else truncate(price, -4) end) as price_group, 
       count(*) as products /* count(product_id) */
from product
group by price_group
order by price_group

문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

* 2021년에 가입한 회원 수 - user_info

* 2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수 : 분자 구할 때는 distinct 를 줘서 중복값 제거

* 반올림 함수 : round()

풀이

select year(o1.sales_date) as YEAR, month(o1.sales_date) as MONTH, 
count(distinct o1.user_id) as PUCHASED_USERS, 
round(count(distinct o1.user_id) / (select count(user_id) from user_info where joined like '2021%'),1) as PUCHASED_RATIO
from online_sale o1
join user_info o2 on o1.user_id = o2.user_id
where o2.joined like '2021%' AND SALES_AMOUNT > 0
group by year, month
order by year, month

비율 구할 때 분모는 select 이중쿼리를 사용해서 전체 회원 수를 따로 구해줬다.

이렇게 하지 않고 바로 count(user_info.user_id)를 분모 자리에 넣으면 join 문의 영향으로 전체 회원 수가 아니라 전체 회원 수 중 구매한 회원 수(즉, 분자 자리에 count(o1.user_id)와 동일)가 되어버리기 때문이다.

문제

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths 3, 3, 4 and 3. The longest name is PQRS, but there are 3 options for shortest named city. Choose ABC, because it comes first alphabetically.

city 이름 길이가 가장 짧은 도시, 가장 긴 도시를 출력하는 문제

limit을 사용하면 쉽게 구할 수 있다.

풀이 코드

order by를 사용해서 도시 이름 길이에 따라 오름차순, 내림차순으로 각각 나열한 후 limit 1을 걸어주면 가장 짧은 길이의 이름을 가진 도시와 가장 긴 길이의 이름을 가진 도시가 출력된다.