주니어 데이터 분석가 면접 질문: SQL, Excel, BI

# VLOOKUP 예제
=VLOOKUP(A2, Products!A:C, 3, FALSE)

# SUMIFS 예제 (지역="East"이고 제품="Widget"인 판매 합계)
=SUMIFS(Sales!C:C, Sales!A:A, "East", Sales!B:B, "Widget")

# INDEX/MATCH 예제 (VLOOKUP보다 유연함)
=INDEX(Prices!C:C, MATCH(A2, Prices!A:A, 0))

# IF를 사용한 조건부 서식
=IF(B2>1000, "High", IF(B2>500, "Medium", "Low"))

# VLOOKUP - A열에서 값 조회, C열에서 반환
=VLOOKUP(A2, A:C, 3, FALSE)

# INDEX/MATCH - 동일하지만 더 유연함
=INDEX(C:C, MATCH(A2, A:A, 0))

# INDEX/MATCH 장점: 왼쪽을 조회 가능
=INDEX(A:A, MATCH(C2, C:C, 0))  # VLOOKUP은 불가능

# 피벗 테이블 구조:
행: 제품 카테고리
열: 분기
값: 판매 합계
필터: 지역

# 피벗 테이블의 계산된 필드
수익 마진 = (수익 - 비용) / 수익

# 날짜 그룹화
날짜를 마우스 오른쪽 버튼으로 클릭 → 그룹 → 월/분기/년 선택

# 1000보다 큰 셀 강조 표시
범위 선택 → 조건부 서식 → 셀 강조 규칙 → 보다 큼

# 색상 척도 (그라데이션)
범위 선택 → 조건부 서식 → 색상 척도

# 데이터 막대
범위 선택 → 조건부 서식 → 데이터 막대

# 사용자 지정 수식
=AND($B2>1000, $C2="Active")

# 중복 제거
데이터 탭 → 중복 제거 → 열 선택

# 조건부 서식을 사용하여 중복 찾기
범위 선택 → 조건부 서식 → 셀 강조 규칙 → 중복 값

# 누락된 데이터 처리
# 옵션 1: 필터링 및 삭제
열 필터링 → (공백) 선택 취소 → 보이는 행 삭제

# 옵션 2: 평균으로 채우기
=IF(ISBLANK(A2), AVERAGE(A:A), A2)

# 옵션 3: 아래로 채우기
범위 선택 → Ctrl+D (Windows) 또는 Cmd+D (Mac)

# 공백 찾아서 바꾸기
Ctrl+H → 찾을 내용: (비워 둠) → 바꿀 내용: 0 또는 N/A

-- 모든 열 선택
SELECT * FROM employees;

-- 특정 열 선택
SELECT first_name, last_name, salary
FROM employees;

-- 별칭으로 선택
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    salary AS "Annual Salary"
FROM employees;

-- 고유 값 선택
SELECT DISTINCT department
FROM employees;

-- 결과 제한
SELECT * FROM employees
LIMIT 10;

-- 간단한 조건
SELECT * FROM employees
WHERE salary > 50000;

-- AND를 사용한 여러 조건
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 60000;

-- OR를 사용한 여러 조건
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- IN 연산자
SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing', 'IT');

-- BETWEEN 연산자
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 80000;

-- LIKE 연산자 (패턴 일치)
SELECT * FROM employees
WHERE first_name LIKE 'J%';  -- J로 시작

-- IS NULL
SELECT * FROM employees
WHERE manager_id IS NULL;

-- NOT 연산자
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Finance');

-- INNER JOIN
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT JOIN (부서가 없는 경우에도 모든 직원)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 여러 JOIN
SELECT 
    e.first_name,
    d.department_name,
    l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id;

-- 자체 JOIN (직원과 관리자)
SELECT 
    e.first_name AS employee,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- 부서별 직원 수 계산
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 부서별 평균 급여
SELECT 
    department,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- HAVING 절 (그룹 필터링)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- 여러 그룹화 열
SELECT 
    department,
    job_title,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;

-- 일반적인 집계 함수
-- COUNT, SUM, AVG, MIN, MAX, COUNT(DISTINCT)

-- WHERE - 그룹화하기 전에 필터링
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 40000  -- 개별 행 필터링
GROUP BY department;

-- HAVING - 그룹화 후 필터링
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;  -- 그룹 필터링

-- 함께 사용
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'  -- 먼저 행 필터링
GROUP BY department
HAVING COUNT(*) > 5;  -- 그런 다음 그룹 필터링

-- 잘못된 예:
-- SELECT department FROM employees
-- WHERE COUNT(*) > 5;  -- 오류: WHERE에서 집계 함수를 사용할 수 없음

import numpy as np

data = [1, 2, 2, 3, 4, 5, 100]

mean = np.mean(data)  # 16.71 (이상치 100의 영향)
median = np.median(data)  # 3 (이상치의 영향 없음)
# mode = 2 (가장 빈번함)

print(f"Mean: {mean}")
print(f"Median: {median}")

import numpy as np

data = np.array([10, 12, 13, 12, 11, 14, 13, 15, 100, 12])

# IQR 방법
Q1 = np.percentile(data, 25)
Q3 = np.percentile(data, 75)
IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

outliers = data[(data < lower_bound) | (data > upper_bound)]
print(f"Outliers: {outliers}")  # [100]

# Z-점수 방법
mean = np.mean(data)
std = np.std(data)
z_scores = np.abs((data - mean) / std)
outliers_z = data[z_scores > 3]
print(f"Outliers (Z-score): {outliers_z}")

-- SQL에서 상관관계 계산 (단순화)
SELECT 
    CORR(sales, temperature) AS correlation
FROM daily_data;

-- 양의 상관관계: 둘 다 함께 증가
-- 음의 상관관계: 하나가 증가하면 다른 하나는 감소
-- 0 상관관계: 관계 없음

# 공식
백분율 변화 = ((새 값 - 이전 값) / 이전 값) × 100

# Excel 수식
=(B2-A2)/A2*100

# 예시:
이전 값: 100
새 값: 120
변화: (120-100)/100 = 0.20 = 20% 증가

# 전년 대비 성장률
=(Sales_2023 - Sales_2022) / Sales_2022 * 100

-- SQL 백분율 변화
SELECT 
    year,
    revenue,
    LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
    ((revenue - LAG(revenue) OVER (ORDER BY year)) / 
     LAG(revenue) OVER (ORDER BY year) * 100) AS pct_change
FROM annual_sales;