240702(+27) | SQL 스터디 코드카타 | leetcode 첫 풀이!

leetcode

• 문제에서 요구하는 컬럼명을 지켜줘야지 제대로 된 채점이 가능하다.
• 회원 가입 후 이메일 유효성 검증을 걸쳐야 코드 실행이 가능하다.

Q84. 미구매 고객 조회

[문제] user_visits와 user_transactions 테이블에서 거래를 하지 않고 방문한 사용자의 ID와 방문 한 횟수 조회

select c_id as customer_id,
       count(*) as count_no_trans
from
(
select v.customer_id c_id,
       t.amount amount #NULL값 확인
from visits v
left join transactions t
on v.visit_id = t.visit_id
where t.amount is null
) sub1
group by 1

                             코드 테스트 창에선 output이 제대로 나오는데

                           submit 하니깐 틀렸다고 나옴. 왜지 -.-

amount에 null값이 들어 있어서 여기 조건을 걸었는데, 정답 보니 visit_id가 null값인 조건문을 건다. 내가 놓친 부분이 있는듯..
amount를 count 했을 때 visit_id가 5인 customer_id도 카운더 하는 것 같다.

• 가설1. left join에 대한 이해 부족?

select v.customer_id c_id, #방문한 고객
        t.visit_id, #구매한 고객
        t.amount amount #소비한 금액
    from visits v
    left join transactions t
    on v.visit_id = t.visit_id

• 가설2. amount의 null값이 아닌 visit_id의 null값 구하기

SELECT 
    v.customer_id AS customer_id, 
    COUNT(*) AS count_no_trans
FROM 
    visits v
LEFT JOIN 
    transactions t 
    ON v.visit_id = t.visit_id
WHERE 
    t.visit_id IS NULL #구매 기록이 없는 고객
GROUP BY 
    1

                    *연속해서 submit하면 runtime이 줄어듬. 의미없는 수치인듯.

Q86. 평균 작동 시간

[문제] machine_id별로 평균 처리 시간을 계산하고, 이를 소수점 세 자리까지 반올림하여 processing_time으로 표시하는 SQL 쿼리를 작성하세요. (결과 테이블은 각 기계의 ID(machine_id)와 해당 기계의 평균 처리 시간(processing_time)을 포함해야 합니다.)

• machine_id 별로 기계의 작동 시작과 마감 시간이 activity_type에 저장되어 있다.

select
    diff.machine_id,
    round(avg(diff.processing_time),3) processing_time
from
    (
        select
        e.machine_id, e.process_id,
        e.timestamp - s.timestamp as processing_time
    from
        (
            select machine_id,
            process_id,
            timestamp
        from activity
        where activity_type = 'end'
        ) e
    join
        (
            select machine_id,
            process_id,
            timestamp
        from activity
        where activity_type = 'start'
        ) s
    on s.machine_id = e.machine_id
    ) diff
group by 1

• 조건을 지정한 두개의 쿼리 문들을 결합하여 사용하기 위해 join 문을 사용 했다.
• 소수점 아래의 3번째 까지 출력하기 위해, round() 함수를 사용했다.
  

큰 수 작은 수 접근법

SELECT a.machine_id
     , ROUND(AVG(b.timestamp - a.timestamp),3) As processing_time
FROM activity a, activity b
WHERE a.machine_id = b.machine_id
AND a.process_id = b.process_id
AND a.timestamp < b.timestamp
GROUP BY 1;

문제에 매몰 되지 않고, 데이터의 성질이 어떤가에 생각해보면, 신박한 생각법으로 간략히 작성 할 수도 있다 👍

Q87. 작은 보너스

[문제] 보너스 금액이 1000 미만인 직원의 이름과 보너스 금액

select e.name, b.bonus from employee e
left join bonus b
on e.empid = b.empid
where b.bonus < 1000 or b.bonus is null