☑️ 전국 카페 주소 데이터 정제하기[문제]주소 컬럼에서 행정구역별 컬럼 분리 후 카페 갯수 집계 하기[문제 풀이]내 코드 SELECT SUBSTRING_INDEX(address,' ',1) AS sido ,SUBSTRING_INDEX(SUBSTRING_INDEX(address,' ',2),' ',-1) AS sigungu ,COUNT(DISTINCT cafe_id) AS cntFROM cafesGROUP BY sido,sigunguORDER BY 3 DESC 이슈 및 해결 과정 아이디어 : 주소 컬럼에서 행정구역별 추가 컬럼 생성 후 집계행정구역 분리 : `공백` 기준으로 python 에서 `SPLIT` 함수 쓰는이 분리 하면 됨 ex) address 컬럼 값이 '서울시 송파구 오금동..

☑️  문제1 . 조건별 연도별 매출 집계[문제][문제 풀이]내 코드 SELECT release_year ,sum(revenue) AS revenueFROM movies m WHERE release_year >=2012 AND VOTE_COUNT >=100 AND JSON_LENGTH(genres)>=2 GROUP BY release_year ORDER BY 1; 이슈 및 해결 과정 단순히 조건 필터링후 집계 하는 문제장르 컬럼이 JSON 타입으로 저장 → 2개이상 조건 필터링시 json 타입의 길이 반환해주는 함수 적용`JSON_LENGTH(genres)``JSON_LENGTH(json_doc)`json 타입의 길이 반환해주는 함수, 유효한 표현식이 아닐경우 에러남중첩된 배열 계산 안함mysq..

☑️  펭귄 날개와 몸무게의 상관 계수[문제]펭귄 날개와 몸무게의 피어슨 상관 계수 계산하기소수점 3째 까지 [문제 풀이]내 코드 WITH avgg as (SELECT species ,flipper_length_mm - AVG(flipper_length_mm) OVER (PARTITION BY species) AS dev_flipper ,body_mass_g - AVG(body_mass_g) OVER (PARTITION BY species) AS dev_bodyFROM penguins)SELECT species ,ROUND(SUM(dev_flipper * dev_body) / (SQRT(SUM(dev_flipper*dev_flipper)) * SQRT(SUM(de..

15 Days of Learning SQL | HackerRankfind users who submitted a query every day.www.hackerrank.com☑️  15 Days of Learning SQL[문제]Julia conducted a  days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.Write a query to print total number of unique hackers who made at least  submission each day (starting on the first day of the contest),..

Print Prime Numbers | HackerRankPrint prime numbers.www.hackerrank.com ☑️  Print Prime Numbers[문제]Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).For example, the output for all prime numbers  would be:2&3&5&71-1000 가지 숫자 중 소수만 찾아 앰퍼센트로 연결해서 하나의 셀에 출력[문제 풀이]내 코드 WIT..

Draw The Triangle 1 | HackerRankDraw the triangle pattern using asterisks.www.hackerrank.com☑️  Draw The Triangle 1[문제]'*' 특수문자 반복하여 직각 삼각형 구현하는 문제[문제 풀이]내 코드 SELECT REPEAT('* ',20) UNION ALL SELECT REPEAT('* ',19) UNION ALL SELECT REPEAT('* ',18) UNION ALL SELECT REPEAT('* ',17) UNION ALL SELECT REPEAT('* ',16) UNION ALL SELECT REPEAT('* ',15) UNION ALL SELECT REPEAT('* ',14)UNION ALL SELECT RE..

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  Placements | HackerRankWrite a query to output the names of those students whose best friends got offered a higher salary than them.www.hackerrank.com ☑️  SQL Project Planning[문제]If the End_Date of the tas..

Challenges | HackerRankPrint the total number of challenges created by hackers.www.hackerrank.com☑️  Challenges[문제]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 more than one student created the same numbe..

☑️  1484. Group Sold Products By The Date[문제]Write a solution to find for each date the number of different products sold and their names.Return the result table ordered by sell_date.The sold products names for each date should be sorted lexicographically.날짜별 팔린 제품 갯수 집계 및 팔린 제품명 한 컬럼에 기재 [문제 풀이]내 코드 → 끝까지 못 품 (실패)WITH rownt AS (SELECT sell_date ,product ,ROW_NUMBER() OVER (PARTITION ..

☑️  1341. Movie Rating[문제]Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.두 가지 조건에 해당하는 출력값 각각 집계후 합쳐주기 ( UNION 사용) [문제 풀이]내 코드 (SELECT u.name AS results-- 각각에서 찾아주고, 출력값 연결해..