본문 바로가기

SQL

** HackerRank / Advanced Join / Interviews / MySQL ** 2024. 1. 20. 18:56 📚문제 https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true Interviews | HackerRank find total number of view, total number of unique views, total number of submissions and total number of accepted submissions. www.hackerrank.com 문제가 길고 예제 테이블이 많아서 링크로 대체 📝풀이 SELECT con.contest_id , con.hacker_id , con.name , SUM(total_submissions) , SUM(total_accepted_submissions) , SUM(total..
HackerRank / Alternative Queries / Print Prime Numbers / MySQL 2024. 1. 18. 11:49 📚문제 📝풀이 SELECT GROUP_CONCAT(NUMB SEPARATOR '&') FROM ( SELECT @num:=@num+1 as NUMB FROM information_schema.tables t1, information_schema.tables t2, (SELECT @num:=1) tmp ) tempNum WHERE NUMB
HackerRank / Advanced Join / SQL Project Planning / MySQL 2024. 1. 17. 15:28 📚문제 https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true SQL Project Planning | HackerRank Write 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 Start_Date , MIN(End_Date) FROM ((SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SEL..
Hackerrank / Basic Join / Ollivander's Inventory / MySQL 2024. 1. 10. 10:29 📚문제 https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true Ollivander's Inventory | HackerRank Help pick out Ron's new wand. www.hackerrank.com 📝풀이 SELECT w.id , p.age , w.coins_needed , w.power FROM Wands w INNER JOIN Wands_Property p ON w.code = p.code WHERE (p.age,w.power,w.coins_needed) IN (SELECT p.age AS age , w.power AS power , MIN(w.coins_needed) AS min_co..
Hackerrank / Basic Join / Top Competitors / MySQL 2024. 1. 9. 11:33 📚문제 https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true Top Competitors | HackerRank Query a list of top-scoring hackers. www.hackerrank.com 이번 문제는 너무 길어서 캡쳐하기 번거로워서 링크 첨부 📝풀이 SELECT id , name FROM (SELECT h.hacker_id as id , h.name as name , COUNT(DISTINCT s.challenge_id) AS chal FROM Submissions s INNER JOIN Challenges c ON s.challenge_id = c.challenge_id INNER JOIN Diff..
**Hackerrank / Aggregation / Weather Observation Station 20 / MySQL ** 2024. 1. 8. 17:47 📚문제 📝풀이 SELECT ROUND(AVG(LAT_N),4) FROM (SELECT LAT_N , ROW_NUMBER() OVER (ORDER BY LAT_N) AS RN , COUNT(*) OVER() + 1 AS CNT FROM STATION) etc WHERE RN BETWEEN FLOOR(CNT/2) AND CEIL(CNT/2) MySQL에는 중간값을 구하는 함수가 따로 없어서 직접 구해야한다 SQL로 중위값 조건을 구하는게 까다로워서 풀이 참고.. 참고링크 출처 : https://yurimyurim.tistory.com/14 📋풀이 순서 1. LAT_N(위도)의 오름차순으로 각 행마다 중복되지 않는 번호 부여 ROW_NUMBER() OVER (ORDER BY LAT_N) AS RN 2. 중간값..
LeetCode / 1624. Largest Substring Between Two Equal Characters / Python / Easy 2023. 12. 31. 12:03 📚문제 📝풀이 class Solution(object): def maxLengthBetweenEqualCharacters(self, s): dic = {} ans = -1 for idx,k in enumerate(s): if k in dic: ans = max(ans,idx-dic[k]-1) else: dic[k] = idx return ans LeetCode에 있는 파이썬 문제인데 Daily로 1개씩 Update 되는 것 같다 전에 나온 문제는 너무 어려워서 풀이를 봐도 이해가 안되서 스킵했다 이 문제도 생각보다 어려웠다... 대칭되는 두 문자가 꼭 문자열 양 끝에 나오는 게 아니라서 그 부분이 조금 까다로웠다 그래서 게시판도 참고하고 구글링 해보다가 제일 간단하고 직관적인 풀이를 조금 참고하였다 풀이..
LeetCode / 1084. Sales Analysis III / MySQL / Easy 2023. 12. 28. 18:50 📚문제 📝풀이 # Write your MySQL query statement below SELECT DISTINCT s.product_id , p.product_name FROM Sales s INNER JOIN Product p ON s.product_id = p.product_id WHERE s.sale_date BETWEEN '2019-01-01' AND '2019-03-31' AND s.product_id NOT IN (SELECT DISTINCT product_id FROM Sales WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31') 처음에는 WHERE절 서브쿼리에서 계속 오답이 발생해서 WHERE sale_date > '2019-03-31..
LeetCode / 607.Sales Person / MySQL / Easy 2023. 12. 27. 10:53 📚문제 📝풀이 # Write your MySQL query statement below SELECT name FROM SalesPerson WHERE sales_id NOT IN (SELECT DISTINCT o.sales_id FROM Orders o JOIN Company c ON o.com_id = c.com_id AND c.name = 'RED' 정답처리 된 풀이 처음 풀이가 왜 안풀리는지 잘 이해가 되지 않아서 기록 # Write your MySQL query statement below SELECT name FROM SalesPerson WHERE sales_id NOT IN (SELECT DISTINCT o.sales_id FROM Orders o WHERE com_id = 1) JOIN 하..
LeetCode / 1321. Restaurant Growth / MySQL / Medium / FROM절 서브쿼리 , Window 함 2023. 12. 24. 09:29 📚문제 📝풀이 # Write your MySQL query statement below SELECT visited_on , amount , ROUND(amount/7,2) AS average_amount FROM (SELECT DISTINCT visited_on , SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS amount FROM Customer) AS sum_amount WHERE visited_on >= (SELECT MIN(visited_on) FROM Customer) + INTERVAL 6 DAY FROM절 서브쿼리와 WINDOW 함수를 활용 다른 방법도 많이 있지만 다..

티스토리툴바