1. sql 코드카타(92~96번)
92번 - 평균 판매 가격 계산
문제 정리
Prices테이블에는 상품의 가격 정보가 있으며, 각product_id의 특정 기간 동안 가격이 유지됨.UnitsSold테이블에는 상품이 판매된 날짜와 수량이 저장됨.- 각
product_id별로 판매된 상품의 평균 가격을 구하고, 소수점 둘째 자리까지 반올림하여 출력.
내 정답 쿼리
SELECT p.product_id,
IFNULL(ROUND(SUM(units * price) / SUM(units), 2), 0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u
ON p.product_id = u.product_id
AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;쿼리 설명
LEFT JOIN을 사용하여Prices와UnitsSold를product_id및purchase_date조건으로 연결.SUM(units * price) / SUM(units)로 가중 평균을 계산하고ROUND(..., 2)로 반올림.- 판매 기록이 없는 경우
IFNULL(..., 0)을 사용해 평균 가격을 0으로 설정.
93번 - 프로젝트별 평균 근속 연수
문제 정리
Project테이블에는 프로젝트 ID와 참여한 직원 ID가 저장됨.Employee테이블에는 직원 ID와 직원의 근속 연수(experience_years)가 저장됨.- 각 프로젝트별 참여 직원들의 평균 근속 연수를 구하여 소수점 둘째 자리까지 출력.
내 정답 쿼리
SELECT p.project_id,
ROUND(AVG(e.experience_years), 2) average_years
FROM Project p
JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id;쿼리 설명
JOIN을 사용해Project와Employee를employee_id기준으로 연결.AVG(experience_years)를 사용해 프로젝트별 평균 근속 연수를 계산하고ROUND(..., 2)로 반올림.GROUP BY project_id로 프로젝트별 그룹화.
94번 - 콘테스트 등록 비율
문제 정리
Users테이블에는 유저 ID가 저장됨.Register테이블에는 각 유저가 등록한 콘테스트 정보가 저장됨.- 각 콘테스트 별 등록된 유저 비율을 구하고, 소수점 둘째 자리까지 반올림하여 출력.
내 정답 쿼리
SELECT contest_id,
ROUND(COUNT(DISTINCT user_id) / (SELECT COUNT(DISTINCT user_id) FROM Users) * 100, 2) AS percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id;쿼리 설명
COUNT(DISTINCT user_id)로 콘테스트별 등록된 유저 수를 계산.- 전체 유저 수 대비 비율을 구한 후
ROUND(..., 2)로 반올림. ORDER BY percentage DESC, contest_id ASC로 정렬.
95번 - 쿼리 품질 분석
문제 정리
Queries테이블에는 쿼리명, 결과값, 검색 위치(position), 평점(rating)이 저장됨.- 쿼리 품질(
quality)은rating / position의 평균. - 낮은 평점(
rating < 3)을 가진 쿼리의 비율을 구하여 출력.
내 정답 쿼리
SELECT query_name,
ROUND(AVG(rating / position), 2) quality,
ROUND(SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) poor_query_percentage
FROM Queries
GROUP BY query_name;쿼리 설명
AVG(rating / position)을 사용해 품질(quality)을 계산.SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) / COUNT(*) * 100으로 낮은 평점 비율을 구하고ROUND(..., 2)적용.GROUP BY query_name을 사용해 쿼리별 품질을 분석.
96번 - 월별 트랜잭션 분석
문제 정리
Transactions테이블에는 각 거래의 국가, 승인 여부(state), 금액이 저장됨.- 월별(
YYYY-MM), 국가별 총 거래 수, 승인된 거래 수, 총 거래 금액, 승인된 거래 금액을 출력.
내 정답 쿼리
SELECT DATE_FORMAT(trans_date, '%Y-%m') month,
country,
COUNT(1) trans_count,
SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) approved_count,
SUM(amount) trans_total_amount,
SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) approved_total_amount
FROM Transactions
GROUP BY 1, 2;쿼리 설명
DATE_FORMAT(trans_date, '%Y-%m')을 사용해 월별 그룹화.COUNT(1)로 전체 거래 수를 구하고,SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END)로 승인된 거래 수를 계산.SUM(amount)로 전체 거래 금액을 구하고, 승인된 거래 금액만 따로SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END)로 계산.
- 아래는 벨로그 포스팅용으로 문제 / 내 정답 코드 / 코드 설명을 정리한 내용입니다.
그대로 복사해서 벨로그에 올리면 됩니다.
2.항공편 데이터 분석 (Python Pandas)
Python의 Pandas 라이브러리를 활용하여 항공편 데이터를 분석하는 문제를 해결해보자.
아래 문제들을 하나씩 풀면서 데이터 처리 방법을 익혀보자.
문제 1: 데이터 불러오기
문제 설명
- Python 라이브러리를 활용하여 CSV 파일을 DataFrame으로 읽기
- 행과 열 개수 출력
- 처음 5줄 출력
내 정답 코드
# 1. 라이브러리 불러오기
import pandas as pd
# 2. CSV 파일 불러오기
data = pd.read_csv('C:/python_ex/스파르타/flight_data_homework.csv')
# 3. 데이터 크기 출력
print(f"테이블의 행과 열 개수 : {data.shape}")
# 4. 처음 5줄 출력
print(f"테이블의 처음 5줄 : \n {data.head()}")코드 설명
pd.read_csv(file_path)→ CSV 파일을 DataFrame으로 불러옴data.shape→ 데이터의 (행, 열) 개수 출력data.head()→ 데이터의 앞 5개 행 출력
문제 2: 결측치 처리
문제 설명
- 각 컬럼별 결측치 개수 확인
- 결측치가 있는 행 제거
내 정답 코드
# 1. 각 컬럼별 결측치 개수 출력
print(f"각 컬럼별 결측치 개수:\n{data.isna().sum()}")
# 2. 결측치가 있는 행 제거
df = data.dropna()
# 3. 결측치 제거 후 데이터 크기 출력
print(f"결측치 제거 후 각 컬럼별 결측치 개수:\n{df.isna().sum()}")코드 설명
data.isna().sum()→ 각 컬럼별 결측치 개수 확인data.dropna()→ 결측치가 있는 행 제거df.isna().sum()→ 제거 후 다시 결측치 개수 확인
문제 3: 조건에 맞는 데이터 추출하기
문제 설명
- Destination 컬럼 기준 Price의 평균값과 중앙값을 동시에 구하기
- 값은 소수점 첫 번째 자리까지 표현
- Airline, Total_Stops 기준 Route 컬럼을 중복값 없이 추출
- 새로운 DataFrame
df2로 저장하고 인덱스를 재정렬
- 새로운 DataFrame
내 정답 코드
import pandas as pd
import numpy as np
# 1. Destination별 Price의 평균 & 중앙값 계산 (소수점 첫째 자리 반올림)
price_mean_median = round(df.groupby('Destination')['Price'].agg(['mean','median']),1)
print(f"Destination의 평균&중앙값 :\n {price_mean_median}" )
# 2. Airline, Total_Stops 기준으로 Route 중복 제거 후 정렬 + 인덱스 재정렬
df2 = df[['Airline', 'Total_Stops', 'Route']].drop_duplicates().sort_index().reset_index(drop=True)
print(f"중복값 제거 :\n {df2}")코드 설명
groupby('Destination')['Price'].agg(['mean','median'])→ 목적지(Destination)별 평균 및 중앙값 계산round(..., 1)→ 소수점 첫 번째 자리까지 반올림drop_duplicates()→ 중복된 Route 제거sort_index().reset_index(drop=True)→ 정렬 후 새로운 인덱스 부여
문제 4: 조건에 맞는 데이터 추출하기 2
문제 설명
- 출발지(
Source)와 도착지(Destination)를 기준으로 Airline 개수를 카운트하는 피벗 테이블을 만들고, 내림차순으로 정렬하시오. - "Air India" 항공사 중에서 가격(
Price)이 7000 이상인 항공권만 필터링하시오.
내 정답 코드
import pandas as pd
import numpy as np
# 1. 출발지(Source)와 도착지(Destination)를 기준으로 Airline 개수를 카운트하는 피벗 테이블 생성 + 내림차순 정렬
pivot_table = df.pivot_table(index=['Source','Destination'], values="Airline", aggfunc="count").sort_values("Airline", ascending=False)
print(f"피봇테이블 :\n {pivot_table}")
# 2. Airline이 "Air India"이고 Price가 7000 이상인 데이터 필터링
df3 = df[(df['Airline'] == 'Air India') & (df['Price'] >= 7000)]
print(f"필터링 : \n{df3}")코드 설명
pivot_table(index=['Source', 'Destination'], values="Airline", aggfunc="count")
→ 출발지-도착지별 Airline 개수 카운트sort_values("Airline", ascending=False)
→ 가장 많은 항공편이 존재하는 출발지-도착지 조합을 위로 정렬df[(df['Airline'] == 'Air India') & (df['Price'] >= 7000)]
→ "Air India" 항공편 중에서 가격이 7000 이상인 항공권만 필터링
출력 예시:
피봇테이블 :
Airline
Source Destination
Delhi Cochin 250
Kolkata Banglore 200
Banglore Delhi 180
Delhi Hyderabad 150
Banglore Kolkata 140
필터링 :
Airline Date_of_Journey Source Destination Route Dep_Time Arrival_Time Duration Total_Stops Additional_Info Price
1 Air India 1/05/2019 Kolkata Banglore CCU → IXR → BBI → BLR 05:50 13:15 7h 25m 2 stops No info 7662
3 Air India 12/05/2019 Kolkata Banglore CCU → NAG → BLR 18:05 23:30 5h 25m 1 stop No info 8010
7 Air India 3/04/2019 Delhi Cochin DEL → BOM → COK 08:00 12:20 4h 20m 1 stop No info 8500아래는 벨로그 포스팅용 정리입니다.
문제 / 내 정답 코드 / 코드 설명을 깔끔하게 정리했으니, 그대로 복사해서 올리면 됩니다.
도전 1: 조건에 맞는 데이터 추출하기 3
문제 설명
Date_of_Journey컬럼을 기준으로 수요일(Wednesday)에 예약된 경우의 평균 가격을 구하시오.pandas.to_datetime()을 활용하여 날짜를 변환하고dt.day_name()으로 요일을 추출하시오.
내 정답 코드
import pandas as pd
df['Date_of_Journey'] = pd.to_datetime(df['Date_of_Journey'], infer_datetime_format=True)
df['Day'] = df['Date_of_Journey'].dt.day_name()
wednesday_price_mean = df[df['Day'] == 'Wednesday']['Price'].mean()
print(f"수요일 가격 평균: {round(wednesday_price_mean, 1)}")코드 설명
pd.to_datetime(df['Date_of_Journey'], infer_datetime_format=True)
→ 날짜 데이터를 datetime 형식으로 변환df['Date_of_Journey'].dt.day_name()
→ 요일을 추출하여Day컬럼 생성df[df['Day'] == 'Wednesday']['Price'].mean()
→ 수요일에 예약된 항공권만 필터링하고Price평균 계산round(wednesday_price_mean, 1)
→ 소수점 첫 번째 자리까지 반올림
출력 예시
수요일 가격 평균: 8450.7도전 2: 조건에 맞는 데이터 추출하기 4
문제 설명
Dep_Time컬럼을 기준으로 출발 시간대(아침, 낮, 오후, 밤)로 분류하시오.- 각 시간대별 항공편 개수를 카운트하시오.
lambda함수를 사용하여 한 줄로 if-else 조건문을 작성하시오.
내 정답 코드
import pandas as pd
df['hour'] = pd.to_datetime(df['Dep_Time'], format="%H:%M").dt.hour
df['Time_category'] = df['hour'].apply(lambda x: "아침" if 5 <= x < 12 else
"낮" if 12 <= x < 18 else
"오후" if 18 <= x < 24 else
"밤")
time_count = df['Time_category'].value_counts()
print(f"시간별 항공편 개수 : \n{time_count}")코드 설명
pd.to_datetime(df['Dep_Time'], format="%H:%M").dt.hour
→ 출발 시간(Dep_Time)을 datetime 형식으로 변환 후, 시(hour)만 추출df['hour'].apply(lambda x: ...)
→lambda함수를 사용하여 비행 시간을 시간대(아침, 낮, 오후, 밤)로 분류df['Time_category'].value_counts()
→ 각 시간대별 항공편 개수를 카운트
출력 예시
시간별 항공편 개수 :
아침 3200
낮 2900
오후 2800
밤 1800
Name: Time_category, dtype: int64
아자아자