처음으로 코드시그널에서 코딩테스트를 볼 기회가 있어서 코드시그널을 처음 접했다 👻
그래서 코드시그널 사이트의 코딩테스트 환경과 익숙해질겸 Databases에 있는 SQL 문제들을 풀어보았다.
https://app.codesignal.com/arcade/db
총 84 문제가 있었고 3일 동안 꾸준히 약 20-30 문제씩 풀어서 완료했다.
다 정리하기엔 너무 많고 기억해두면 좋을 것들 위주로 적어보려고 한다.
Order
contestLeaderboard
문제
You are working as a recruiter at a big IT company, and you're actively looking for candidates who take the top places in major programming contests. Since the grand finale of the annual City Competition, you've been reaching out to the top participants from the leaderboard, and successfully so.
You have already interviewed all the prize winners (the top 3 participants), but that's not enough right now. Your company needs more specialists, so now you would like to connect with the participants who took the next 5 places.
The contest leaderboard is stored in a table leaderboard with the following columns:
id: unique id of the participant;
name: the name of the participant;
score: the score the participant achieved in the competition.
The resulting table should contain the names of the participants who took the 4th to 8th places inclusive, sorted in descending order of their places. If there are fewer than 8 participants, the results should contain those who ranked lower than 3rd place.
It is guaranteed that there are at least 3 prize winners in the leaderboard and that all participants have different scores.--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
SELECT NAME
FROM (SELECT NAME, SCORE, RANK () OVER (ORDER BY SCORE DESC) AS RK
FROM LEADERBOARD) RK_TBL
WHERE RK >= 4 AND RK <=8;
END
--- 다른 분 답 참고
CREATE PROCEDURE solution()
BEGIN
select name
from leaderboard
order by score desc
limit 5 offset 3;
END- 4등부터 8등까지 추출하기 위해서는 RANK () OVER 혹은 LIMIT OFFSET 활용 가능
1) RANK () OVER 을 사용해 SCORE 순으로 나열한 뒤, RANK가 4부터 8인 것 추출
2) LIMIT OFFSET 사용해 3행 그 이후부터 5개 행 추출 (LIMIT 5 OFFSET 3: 0이 가장 첫 번째 행이고, 네 번째 행을 얻고 싶다면 위치 3으로 지정)
gradeDistribution
문제
At the end of every semester your professor for "Introduction to Databases" saves the exam results of every student in a simple database system. In the database table Grades, there are five columns:
Name: the name of the student;
ID: the student's ID number (a 5 byte positive integer);
Midterm1: the result of the first midterm out of 100 points;
Midterm2: the result of the second midterm out of 100 points;
Final: the result of the final exam, this time out of a possible 200 points.
According to school policy, there are three possible ways to evaluate a grade:
Option 1: Grade = 0.25 * Midterm1 + 0.25 * Midterm2 + 0.5 * Final;
Option 2: Grade = 0.5 * Midterm1 + 0.5 * Midterm2;
Option 3: Grade = Final.
Each student's final grade comes from the option that works the best for that student.
As a Teaching Assistant (TA), you need to query the name and id of all the students whose best grade comes from Option 3, sorted based on the first 3 characters of their name. If the first 3 characters of two names are the same, then the student with the lower ID value comes first.--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
SELECT NAME, ID
FROM (SELECT NAME
, ID
, MIDTERM1*0.25 + MIDTERM2*0.25 + FINAL*0.5 AS OPTION1
, MIDTERM1*0.5 + MIDTERM2*0.5 AS OPTION2
, FINAL AS OPTION3
FROM GRADES) G_TBL
WHERE OPTION3 > OPTION2 AND OPTION3 > OPTION1
ORDER BY LEFT(NAME, 3), ID ;
END
--- 다른 분 답 참고
CREATE PROCEDURE solution()
SELECT NAME, ID
FROM GRADES
WHERE GREATEST(MIDTERM1/4+MIDTERM2/4+FINAL/2, MIDTERM1/2+MIDTERM2/2)<Final
ORDER BY LEFT(NAME, 3), ID ;
END1) 각 Option에 대한 점수 계산한 서브쿼리 사용하여, Option3가 Option1과 Option2보다 큰 경우 추출하고 이름 앞 세 자리, 아이디에 따라 정렬
2) 서브쿼리 쓰지 않고 Where 절으로 Option1, Option2 중 더 큰 것이 Option3 보다 작은 경우만 추출 (여기서 GREATEST 활용해 가장 큰 수 추출 가능, GREATEST의 반대는 LEAST)
mischievousNephews
문제
Your nephews Huey, Dewey, and Louie are staying with you over the winter holidays. Ever since they arrived, you've hardly had a day go by without some kind of incident - the little rascals do whatever they please! Actually, you're not even mad; the ideas they come up with are pretty amazing, and it looks like there's even a system to their mischief.
You decided to track and analyze their behavior, so you created the mischief table in your local database. The table has the following columns:
mischief_date: the date of the mischief (of the date type);
author: the nephew who caused the mischief ("Huey", "Dewey" or "Louie");
title: the title of the mischief.
It looks like each of your nephews is active on a specific day of the week. You decide to check your theory by creating another table as follows:
The resulting table should contain four columns, weekday, mischief_date, author, and title, where weekday is the weekday of mischief_date (0 for Monday, 1 for Tuesday, and so on, with 6 for Sunday). The table should be sorted by the weekday column, and for each weekday Huey's mischief should go first, Dewey's should go next, and Louie's should go last. In case of a tie, mischief_date should be a tie-breaker. If there's still a tie, the record with the lexicographically smallest title should go first.
It is guaranteed that all entries of mischief are unique.--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
SELECT WEEKDAY(MISCHIEF_DATE) WEEKDAY, MISCHIEF_DATE, AUTHOR, TITLE
FROM MISCHIEF
ORDER BY 1, FIELD(AUTHOR, 'Huey', 'Dewey', 'Louie'), 2, 4;
END- Order 절에 FIELD 사용해 각 값에 대한 정렬 가능: FIELD(컬럼, 값1, 값2, 값3)
Group
travelDiary
문제
You are an avid traveler and you've visited so many countries that when people ask you where you've been, you can't even remember all of them! Luckily, every time you travel somewhere you write down the trip information in your diary. Now you want to get a list of all the different countries that you have visited using the information in your diary.
The diary is represented as a table diary, which has the following columns:
id: the unique ID of the trip;
travel_date: the date the trip began;
country: the country to which you traveled.
Given this diary table, create a semicolon-separated list of all the distinct countries you've visited, sorted lexicographically, and put the list in a table that has a single countries column.-- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
SELECT GROUP_CONCAT(DISTINCT COUNTRY SEPARATOR ';') COUNTRIES
FROM DIARY
ORDER BY COUNTRY;
END- Country 컬럼을 합치고 ;으로 각 Country마다 구분해주어야 하기 때문에 Group_concat로 Country 컬럼 값을 합치고 Separator ';' 을 활용해 ;로 구분: Group_concat(컬럼 Separator 'something')
soccerPlayers
You have a table soccer_team that contains information about the players in your favorite soccer team. This table has the following structure:
id: the unique ID of the player;
first_name: the first name of the player;
surname: the last name of the player;
player_number: the number that the player wears (the number is guaranteed to be unique).
Create a semicolon-separated list of all the players, sorted by their numbers, and put this list in a table under a column called players. The information about each player should have the following format: first_name surname #number.-- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
SELECT GROUP_CONCAT(FIRST_NAME, ' ', SURNAME, ' ', '#', PLAYER_NUMBER
ORDER BY PLAYER_NUMBER
SEPARATOR '; ') PLAYERS
FROM SOCCER_TEAM;
END- first_name surname #number이라는 형식으로 Group_concat하고 separator ; 으로 각 값 구분
marketReport
Your company is planning to expand internationally very soon. You have been tasked with preparing a report on foreign markets and potential competitors.
After some investigation, you've created a database containing a foreignCompetitors table, which has the following structure:
competitor: the name of the competitor;
country: the country in which the competitor is operating.
In your report, you need to include the number of competitors per country and an additional row at the bottom that contains a summary: ("Total:", total_number_of_competitors)
Given the foreignCompetitors table, compose the resulting table with two columns: country and competitors. The first column should contain the country name, and the second column should contain the number of competitors in this country. The table should be sorted by the country names in ascending order. In addition, it should have an extra row at the bottom with the summary, as described above.--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
SELECT IF(GROUPING(COUNTRY),'Total:', COUNTRY) COUNTRY, COUNT(COMPETITOR) COMPETITORS
FROM FOREIGNCOMPETITORS
GROUP BY COUNTRY WITH ROLLUP;
END
--- 다른 분 답 참고
CREATE PROCEDURE solution()
BEGIN
SELECT IFNULL(COUNTRY, "Total:") COUNTRY, COUNT(COMPETITOR) COMPETITORS
FROM FOREIGNCOMPETITORS
GROUP BY COUNTRY WITH ROLLUP;
END- 각 COUNTRY마다 COMPETITOR를 COUNT 해주고, 총계도 RETURN 해야 하는 문제로, GROUP BY와 ROLLUP 사용 가능
- 단, ROLLUP 집계 컬럼의 경우, 데이터가 없는 것도 아닌데 NULL로 보여지는게 문제이기에 다음 두 방법 사용 가능
1) GROUPING 사용해 ROLLUP 집계 컬럼을 'Total: '로 변환
2) IFNULL 사용해 ROLLUP 집계 컬럼을 'Total: '로 변환
성장 일기