문제: LeetCode SQL 116번 - Restaurant Growth
문제 설명
식당 고객들의 방문 기록과 결제 금액이 담긴 테이블이 있다.
각 날짜를 기준으로 그 날짜를 포함한 직전 6일까지의 7일간 매출을 기준으로,
- 총 매출과
- 일일 평균 매출을 구해야 한다.
단, 정확히 7일치 데이터가 있는 경우만 결과에 포함되어야 하며,
결과는 날짜 오름차순으로 정렬되어야 한다.
내 정답 쿼리
WITH A AS (
SELECT visited_on, SUM(amount) AS amount
FROM Customer
GROUP BY visited_on
)
SELECT
a1.visited_on,
SUM(a2.amount) AS amount,
ROUND(SUM(a2.amount) / 7, 2) AS average_amount
FROM A a1
JOIN A a2
ON a2.visited_on BETWEEN DATE_SUB(a1.visited_on, INTERVAL 6 DAY) AND a1.visited_on
GROUP BY a1.visited_on
HAVING COUNT(*) = 7
ORDER BY a1.visited_on;쿼리 설명
WITH A: 날짜별 매출 합계를 먼저 구해서 하루당 1개의 행으로 정리함- 메인 쿼리에서
a1은 기준 날짜,a2는 그 기준 날짜로부터 최근 7일간의 데이터 JOIN은a2.visited_on BETWEEN a1.visited_on - 6일 AND a1.visited_on으로 슬라이딩 윈도우 구성SUM(a2.amount)으로 7일간 총 매출을 구하고,ROUND(... / 7, 2)로 평균 매출 계산HAVING COUNT(*) = 7조건으로 정확히 7일치가 존재할 때만 출력ORDER BY a1.visited_on으로 날짜 오름차순 정렬
python 39번 문제
문제:
최대공약수와 최소공배수
문제설명:
두 수 n, m을 입력받아
- 최대공약수(GCD)
- 최소공배수(LCM)
를 구해서[GCD, LCM]형태로 반환하는 함수 만들기.
LCM은(n * m) // GCD공식 사용.
내 정답:
def solution(n, m):
answer = []
def gcd(a, b):
while b:
a, b = b, a % b
return a
g = gcd(n, m)
l = (n * m) // g
answer = [g, l]
return answer정답 쿼리 설명:
gcd(a, b)함수는 유클리드 호제법으로 최대공약수를 구함.- 반복문에서
a, b = b, a % b를 계속하면서b == 0이 될 때까지 반복 - 그때의
a가 최대공약수
- 반복문에서
- 최소공배수는
(n * m) // g공식으로 계산 [g, l]형식으로 리스트에 담아 리턴
1번 문제: 로그인 횟수별 직원 수
문제 설명
- 2023년 7월 1일부터 9월 30일까지
- 로그인에 성공한 직원만 분석 대상
- 각 직원별 로그인 횟수를 집계하고, 동일한 로그인 횟수를 가진 직원 수를 출력
- 출력 컬럼:
unique_logins,employee_count
정답 쿼리
SELECT unique_logins, COUNT(*) AS employee_count
FROM (
SELECT e.employee_id, COUNT(*) AS unique_logins
FROM logins l
INNER JOIN employees e ON l.employee_id = e.employee_id
WHERE DATE(l.login_time) BETWEEN '2023-07-01' AND '2023-09-30'
AND l.login_result = 'SUCCESS'
GROUP BY e.employee_id
) a
GROUP BY unique_logins
ORDER BY unique_logins;쿼리 설명
- 내부 서브쿼리에서:
- 직원 테이블과 로그인 테이블을 조인하여 유효한 직원만 필터링
SUCCESS로그인만 집계- 로그인 날짜를
DATE()함수로 필터링 (날짜 범위 정확하게 계산) - 직원별 로그인 횟수를 계산 (
COUNT(*))
- 외부 쿼리에서:
- 동일한 로그인 횟수를 가진 직원 수를 다시 집계
GROUP BY unique_logins을 통해 각 그룹별 인원수를 셈ORDER BY로 로그인 횟수 기준 정렬
2번 문제: 세 번째로 높은 급여
문제 설명
- 직원 테이블에서 세 번째로 높은 급여를 받은 직원들 모두 출력
- 동일 급여를 받은 직원이 여러 명이면 모두 포함
- 출력:
employee_id,name,salary
정답 쿼리
WITH ranked_salaries AS (
SELECT
employee_id,
name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employee_salary
)
SELECT employee_id, name, salary
FROM ranked_salaries
WHERE rnk = 3
ORDER BY employee_id;쿼리 설명
DENSE_RANK()윈도우 함수를 사용해 급여 내림차순 순위를 부여- 동일 급여는 같은 순위를 갖기 때문에 3번째로 높은 급여 정확히 찾을 수 있음
WITH절(CTE)로 먼저 순위를 구한 다음- 외부 쿼리에서
rnk = 3만 필터링 ORDER BY employee_id로 정렬
3번 문제: 부서 간 메시지 비율
문제 설명
- 메시지를 보낸 사람과 받은 사람 모두 부서 정보가 존재할 때만 유효
- 부서가 서로 다른 경우를 "부서 간 메시지"로 정의
- 전체 메시지 중 부서 간 메시지 비율(%)을 계산
- 출력:
inter_department_msg_pct
정답 쿼리
SELECT
ROUND(
(SUM(CASE WHEN E.department != P.department THEN 1 ELSE 0 END) / COUNT(*)) * 100,
1
) AS inter_department_msg_pct
FROM messages M
JOIN employees E ON M.sender_id = E.employee_id
JOIN employees P ON M.receiver_id = P.employee_id
WHERE E.department IS NOT NULL AND P.department IS NOT NULL;쿼리 설명
messages테이블의 송신자와 수신자를employees와 각각 조인- 조인 후
department IS NOT NULL조건으로 유효한 메시지만 필터링 CASE WHEN을 이용해 서로 다른 부서이면 1, 같으면 0으로 처리해 합산- 전체 메시지 수 대비 부서 간 메시지 수 비율을 계산
ROUND(..., 1)로 소수 첫째 자리까지 반올림
4번 문제: 전환 유저의 첫 유입 채널
문제 설명
converted = 1인 세션을 가진 유저만 분석 대상- 해당 유저가 가진 모든 세션 중에서
created_at이 가장 이른 세션을 찾음 - 그 세션의 채널(channel)을 출력
- 출력:
user_id,channel
정답 쿼리
SELECT user_id, channel
FROM (
SELECT
u.user_id,
a.channel,
RANK() OVER (PARTITION BY u.user_id ORDER BY u.created_at) AS r
FROM user_sessions u
JOIN ad_attribution a ON u.session_id = a.session_id
WHERE u.user_id IN (
SELECT u2.user_id
FROM user_sessions u2
JOIN ad_attribution a2 ON u2.session_id = a2.session_id
WHERE a2.converted = 1
)
) a
WHERE r = 1
ORDER BY user_id;쿼리 설명
converted = 1조건을 만족하는 유저만 서브쿼리로 추출- 해당 유저가 가진 모든 세션에 대해 유입 채널 정보를 포함시킴
RANK()윈도우 함수로 각 유저의created_at기준으로 세션 순위 부여- 순위가 1인, 즉 가장 처음 방문한 세션만 필터링
- 정렬 조건에 맞춰
user_id기준 오름차순 정렬
아자아자