[업무 지식]/MySQL38 [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. [sum & case when] Queries Quality and Percentage 링크https://leetcode.com/problems/queries-quality-and-percentage/description/ 문제We define query quality as:The average of the ratio between query rating and its position.We also define poor query percentage as:The percentage of all queries with rating less than 3.Write a solution to find each query_name, the quality and poor_query_percentage.Both quality and poor_query_percentage should be rounded.. 2024. 10. 29. [확인 비율 구하기] Confirmation Rate 링크https://leetcode.com/problems/confirmation-rate/description/ 문제The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.Write a solution to find the confirmation rate of each user.Retu.. 2024. 10. 28. 이전 1 2 다음
