본문 바로가기

[스파르타코딩클럽]데이터분석 과정/SQL42

[SQL 코드카타] 178 ~ 180 (HackerRank) 178번 symmetric-pairs with test as (select x, y, row_number() over(order by x) as nr from Functions) -- 1) 구분하고자 row_number 생성 select t1.X, t2.X from test as t1, test as t2 where t1.nr 갑자기 with 절 안 되는게 너무...불편하다ㅠㅠ select ct.contest_id, ct.hacker_id, ct.name, -- 3) contest 기준으로 합계 sum(total_submissions), sum(total_accepted_.. 2024. 2. 2.
[SQL 코드카타] 181~183번(HackerRank) 181번 Draw The Triangle 1 > Set 함수에 대한 이해 부족으로 해답 참고 So, set 함수 복습 및 활용법 이해 SET @num = 21; SELECT repeat('* ', @num := @num - 1) FROM information_schema.tables -- 임시로 사용되는 테이블로 횟수에 따라 반복되는 간단한 작업을 수행할 때 사용 WHERE @num > 1; > with recursive문에 대해서 배움 WITH RECURSIVE rnum AS -- 아래 as n 대신 rnum(n)으로도 가능 (SELECT 20 AS n UNION ALL SELECT n-1 FROM rnum WHERE n > 1) SELECT REPEAT('* ', n) FROM rnum; > 프로시저.. 2024. 2. 1.
[SQL 코드카타] 124~126번(LeetCode) 124번 group-sold-products-by-the-date > Group_concat 활용법 SELECT sell_date, COUNT(DISTINCT product) AS num_sold, GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',' ) AS products FROM Activities GROUP BY sell_date ORDER BY sell_date 125번 list-the-products-ordered-in-a-period SELECT p.product_name, a.unit FROM Products p JOIN (SELECT product_id, sum(unit) AS unit FROM Orders WHERE DATE.. 2024. 1. 31.
[SQL 코드카타] 121 ~ 123번(LeetCode) 121번 patients-with-a-condition SELECT patient_id, patient_name, conditions FROM Patients WHERE conditions like '% DIAB1%' OR conditions like 'DIAB1%' > 정규표현식으로 표현해보기 - \b는 단어 경계를 의미하는데, SQL에서 \는 이스케이프 문자로 표현되어 \\를 두번 입력한 것 SELECT patient_id, patient_name, conditions FROM Patients WHERE conditions regexp '\\bDIAB1' 122번 delete-duplicate-emails DELETE p1 FROM person p1 join person p2 on p1.email =.. 2024. 1. 30.
[SQL 코드카타] 118~120번 (LeetCode) 118번 investments-in-2016 SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016 FROM Insurance WHERE --1) 조건1 tiv_2015가 같은 tiv tiv_2015 IN ( SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(*) > 1 ) AND (lat, lon) IN ( --2) 조건2 lat과 lon이 서로 다른 SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) = 1 ) > ? count 윈도우 함수 사용 select ROUND(sum(tiv_2016), 2) tiv_2016 from ( select *, count.. 2024. 1. 29.
[SQL 코드카타] 175 ~ 177번(HackerRank) 175번 contest-leaderboard > ?? with 절을 사용하는데 계속 오류가 나서 사용하지 못함 select hacker_id, name, sum(score) as sum_score from (select sm.hacker_id, hk.name, sm.challenge_id, max(sm.score) as score from submissions sm join hackers hk on sm.hacker_id = hk.hacker_id group by 1,2,3) abc group by 1,2 having sum(score) > 0 order by 3 desc, 1 176번 sql-projects > 이해 x.. 해답 참고 WITH source AS( SELECT Start_Date, End.. 2024. 1. 24.

티스토리툴바