₍ᐢ•༝•ᐢ₎

문제

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)와 동일)가 되어버리기 때문이다.

문제

배열 arr가 주어집니다. 배열 arr의 각 원소는 숫자 0부터 9까지로 이루어져 있습니다. 이때, 배열 arr에서 연속적으로 나타나는 숫자는 하나만 남기고 전부 제거하려고 합니다. 단, 제거된 후 남은 수들을 반환할 때는 배열 arr의 원소들의 순서를 유지해야 합니다. 예를 들면,

• arr = [1, 1, 3, 3, 0, 1, 1] 이면 [1, 3, 0, 1] 을 return 합니다.
• arr = [4, 4, 4, 3, 3] 이면 [4, 3] 을 return 합니다.

배열 arr에서 연속적으로 나타나는 숫자는 제거하고 남은 수들을 return 하는 solution 함수를 완성해 주세요.

풀이

def solution(arr):
    stack = [arr[0]] #arr 첫번째값 미리 넣어주고
    for i in range(1, len(arr)): 
        if arr[i] != arr[i-1]: # i, i-1 번째 값 비교
            stack.append(arr[i])
    return stack