본문 바로가기

[업무 지식]/MySQL40

[QCC] Cohort-Analysis SQL 문제 1테이블 설명 :students 테이블은 학생에 대한 정보를 담고 있습니다. 테이블 구조와 각 컬럼의 의미는 다음과 같습니다.컬럼명 타입 설명STUDENT_IDINT학생 ID (PK)NAMEVARCHAR학생 이름GENDERCHAR(1)성별 (M/F)AGEINT나이SCOREDECIMAL시험 점수분석해야 할 내용은 다음과 같습니다 :각 성별(GENDER) 기준으로 시험 점수가 높은 상위 3명의 학생 성별, 이름과 점수를 반환하는 SQL 문을 작성하세요.두 학생이 동점일 경우, 나이가 많은 학생을 우선합니다. 결과는 성별(GENDER) 오름차순, 순위 오름차순으로 정렬하여 출력하세요.출력 값 예시students 테이블이 다음과 같다면 :STUDENT_ID NAME GENDER AGE SCORE1Alice.. 2025. 1. 13.
[QCC] Query Challenge Cycle 문제 1 테이블 설명 :movies 테이블은 영화에 대한 정보를 담고 있습니다. 테이블 구조는 다음과 같으며, MOVIE_ID, GENRES, TITLE, POPULARITY, RELEASE_YEAR, REVENUE, VOTE_AVERAGE, VOTE_COUNT은 각각 영화 ID, 영화 장르 리스트, 영화 제목, 인기도, 개봉 연도, 매출, 평균 평점, 평점 수을 나타냅니다.컬럼명 타입 설명MOVIE_IDINT영화 ID (PK)GENRESJSON영화 장르 리스트TITLETEXT영화 제목POPULARITYFLOAT인기도RELEASE_YEARBIGINT개봉 연도REVENUEBIGINT매출VOTE_AVERAGEFLOAT평균 평점VOTE_COUNTINT평점 수분석해야 할 내용은 다음과 같습니다 :2012년 이후.. 2025. 1. 3.
[recursive] Print Prime Numbers 링크https://www.hackerrank.com/challenges/print-prime-numbers/problem Print Prime Numbers | HackerRankPrint prime numbers.www.hackerrank.com 문제정답with recursive tmp as ( select 2 as num union all select num + 1 from tmp where num t2.num and t1.num % t2.num = 0)select group_concat(num separator '&') as numbersfrom tmp where num not in (select num from prime)새롭게 이해한 내용.. 2024. 12. 13.
[recursive] Draw The Triangle 링크https://www.hackerrank.com/challenges/draw-the-triangle-1/problem Draw The Triangle 1 | HackerRankDraw the triangle pattern using asterisks.www.hackerrank.com 문제P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):Write a query to print the pattern P(20). 정답 1set @row_number = 21;select repeat('* ', @row_number := @row_number -1 )from information_schema.tab.. 2024. 12. 12.
[날짜 그룹화] SQL Project Planning 링크https://www.hackerrank.com/challenges/sql-projects/problem SQL Project Planning | HackerRankWrite a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order.www.hackerrank.com 문제정답select min(start_date), max(end_date)from ( select start_date, end_date, date_add(end_date, interval - row_nu.. 2024. 12. 3.
[MAX() over()] Challenges 링크https://www.hackerrank.com/challenges/challenges/problem Challenges | HackerRankPrint the total number of challenges created by hackers.www.hackerrank.com 문제Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If .. 2024. 11. 29.
[row index] Weather Observation Station 20 링크https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true Weather Observation Station 20 | HackerRankQuery the median of Northern Latitudes in STATION and round to 4 decimal places.www.hackerrank.com 문제A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION.. 2024. 11. 25.
[left, right] Weather Observation Station 8 링크https://www.hackerrank.com/challenges/weather-observation-station-8/problem?isFullScreen=true Weather Observation Station 8 | HackerRankQuery CITY names that start AND end with vowels.www.hackerrank.com 문제Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.정답select distinct cityfrom.. 2024. 11. 19.
[REGEXP] Find Users With Valid E-Mails 링크https://leetcode.com/problems/find-users-with-valid-e-mails/description/문제Write a solution to find the users who have valid emails.A valid e-mail has a prefix name and a domain where:The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.The domain is '@leetcode.com'.Return the re.. 2024. 11. 18.
[GROUP_CONCAT] Group Sold Products By The Date 링크https://leetcode.com/problems/group-sold-products-by-the-date/description/문제Write a solution to find for each date the number of different products sold and their names.The sold products names for each date should be sorted lexicographically.Return the result table ordered by sell_date.The result format is in the following example.정답select sell_date, count(distinct product) as num_sold,.. 2024. 11. 18.
[Limit/Offset] Second Highest Salary 링크https://leetcode.com/problems/second-highest-salary/description/문제Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).The result format is in the following example.정답select (select distinct Salary from Employee order by salary desc limit 1 offset 1 ) as SecondHighestSala.. 2024. 11. 18.
[delete] Delete Duplicate Emails 링크https://leetcode.com/problems/delete-duplicate-emails/description/문제Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.정답with soso as( select min(id) as min_id from person group by email)deletefrom personwhere id not in (select min_id from .. 2024. 11. 14.
[LIKE] Patients With a Condition 링크https://leetcode.com/problems/patients-with-a-condition/description/문제Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.Return the result table in any order.The result format is in the following example.정답select *from patientswhere conditions like "DIAB1%"or conditions like "% DIAB1%" .. 2024. 11. 14.
[upper, lower] Fix Names in a Table 링크https://leetcode.com/problems/fix-names-in-a-table/ 문제Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.Return the result table ordered by user_id.The result format is in the following example.정답select user_id, concat(upper(substr(name, 1, 1)), lower(substr(name, 2, length(name)))) as namefrom usersorder by user_id해설upper: 문자를 대문자.. 2024. 11. 14.
[row/between] 프레임 정의 'row'와 'between'의 개념ROWS: 물리적인 행 단위로 윈도우 프레임을 정의합니다. 즉, 현재 행을 기준으로 몇 개의 이전 또는 이후 행을 포함할지 결정합니다.BETWEEN: 프레임의 시작과 끝을 명시적으로 지정하는 데 사용됩니다. BETWEEN 절을 사용하면 윈도우 내에서 어느 범위까지 데이터를 포함할지 설정할 수 있습니다.프레임의 경계(Boundaries)UNBOUNDED PRECEDING: 파티션 내에서 첫 번째 행부터 현재 행까지를 포함합니다.CURRENT ROW: 현재 행만 포함합니다.n PRECEDING: 현재 행 이전의 n개의 행을 포함합니다.n FOLLOWING: 현재 행 이후의 n개의 행을 포함합니다.UNBOUNDED FOLLOWING: 현재 행부터 파티션 내 마지막 행까지 포.. 2024. 11. 12.
[WINDOW] Restaurant Growth 링크https://leetcode.com/problems/restaurant-growth/description/문제You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.Return the result table ordered by visit.. 2024. 11. 11.
[LAG, LEAD] Exchange Seats 링크https://leetcode.com/problems/exchange-seats/description/문제Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.Return the result table ordered by id in ascending order.The result format is in the following example.정답select id, case when id % 2 = 0 then lag(student) over (order by id) .. 2024. 11. 8.
[UNION] Count Salary Categories 링크https://leetcode.com/problems/count-salary-categories/description/ 문제Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:"Low Salary": All the salaries strictly less than $20000."Average Salary": All the salaries in the inclusive range [$20000, $50000]."High Salary": All the salaries strictly greater than $50000.The result table must co.. 2024. 11. 7.
[where & in] Product Sales Analysis III 링크https://leetcode.com/problems/product-sales-analysis-iii/description/문제Write a solution to select the product id, year, quantity, and price for the first year of every product sold.Return the resulting table in any order.The result format is in the following example.정답select product_id, year as first_year, quantity, pricefrom saleswhere (product_id, year) in (select produc.. 2024. 11. 1.
[where & in ] Game Play Analysis IV 링크https://leetcode.com/problems/game-play-analysis-iv/description/문제Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total nu.. 2024. 10. 30.

티스토리툴바