목록Tool/SQL 코딩테스트 풀이 (44)
홍동이의 성장일기
[LeetCode] 601. Human Traffic of Stadium (연속된 id 가져오기)
리트코드에 무료로 나와있는 MEDIUM 레벨의 문제를 모두 해결해서 오늘부터는 HARD 레벨의 문제를 풀며 고급 SQL 문제를 복기 겸 연습해보겠습니다! 🔎 문제 📍 문제 링크: https://leetcode.com/problems/human-traffic-of-stadium/description/ 연속된 ID를 가진 3개 이상의 행으로 레코드를 표시하는 솔루션을 작성하고, 각 행에 대해 100명 이상의 인원이 필요합니다. visit_date별로 주문한 결과표를 오름차순으로 반환합니다. Example 1: Input: Stadium table: +------+------------+-----------+ | id | visit_date | people | +------+------------+------..
[LeetCode] 1934. Confirmation Rate (특정 조건에 부합하는 확률 구하기)
🔎 문제 사용자의 확인율은 '확인'된 메시지 수를 전체 확인 요청 메시지 수로 나눈 값입니다. 확인 메시지를 요청하지 않은 사용자의 확인율은 0입니다. 확인율을 소수점 두 자리로 반올림합니다. 솔루션을 작성하여 각 사용자의 확인률을 찾습니다. 📍 문제 링크: https://leetcode.com/problems/confirmation-rate/description/ 💡 문제 풀이 # 풀이 1번 WITH cte_all AS ( SELECT s.user_id, c.action , CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END as 'cou' FROM signups as s left join confirmations as c on s.user_id = c.user_id..
[LeetCode] 1907. Count Salary Categories (범위에 값이 없으면 0을 반환)
Count Salary Categories - LeetCode Can you solve this real interview question? Count Salary Categories - Table: Accounts +-------------+------+ | Column Name | Type | +-------------+------+ | account_id | int | | income | int | +-------------+------+ account_id is the primary key (column wi leetcode.com SELECT 'Low Salary' as category , COUNT(*) as accounts_count FROM accounts WHERE income < 200..
[LeetCode] 1341. Movie Rating (Union vs. Union All)
Movie Rating - LeetCode Can you solve this real interview question? Movie Rating - Table: Movies +---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | title | varchar | +---------------+---------+ movie_id is the primary key (column w leetcode.com (SELECT u.name as results FROM movierating mr INNER JOIN users u ON mr.user_id = u.user_id GROUP BY u.na..
[LeetCode] 1204. Last Person to Fit in the Bus (누적합계 구하기)
Last Person to Fit in the Bus - LeetCode Can you solve this real interview question? Last Person to Fit in the Bus - Table: Queue +-------------+---------+ | Column Name | Type | +-------------+---------+ | person_id | int | | person_name | varchar | | weight | int | | turn | int | +------------- leetcode.com WITH cte_last AS ( SELECT * , SUM(weight) OVER(ORDER BY turn) as total_weight FROM queu..
[LeetCode] 1193. Monthly Transactions I (Date_format 날짜 함수)
Monthly Transactions I - LeetCode Can you solve this real interview question? Monthly Transactions I - Table: Transactions +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date leetcode.com SELECT DATE_FORMAT(trans_date, '%Y-%m') as month , country , COUNT(*) as trans_count , SUM(CAS..
[LeetCode] 1174. Immediate Food Delivery II (집계함수에 조건 작성하기)
Immediate Food Delivery II - LeetCode Can you solve this real interview question? Immediate Food Delivery II - Table: Delivery +-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | d leetcode.com SELECT ROUND(AVG(order_date = customer_pref_delivery_date)* 100, 2) as immediate_percentag..
[LeetCode] 1164. Product Price at a Given Date (조건에 따라 다른 값 부여하기)
Product Price at a Given Date - LeetCode Can you solve this real interview question? Product Price at a Given Date - Table: Products +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---- leetcode.com SELECT product_id , 10 AS price FROM products GROUP BY product_id HAVING MIN(change_dat..
[LeetCode] 1158. Market Analysis I (JOIN에 AND 조건 사용하기)
Market Analysis I - LeetCode Can you solve this real interview question? Market Analysis I - Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ u leetcode.com SELECT u.user_id as buyer_id , u.join_date , IFNULL(COUNT(o.order_date), 0) AS orders_in_2019 FROM ..
[LeetCode] 585. Investments in 2016
Investments in 2016 - LeetCode Can you solve this real interview question? Investments in 2016 - Table: Insurance +-------------+-------+ | Column Name | Type | +-------------+-------+ | pid | int | | tiv_2015 | float | | tiv_2016 | float | | lat | float | | lon | float | +------------- leetcode.com WITH cte_2015 AS ( -- tiv_2015가 동일한 고객이 두 명 이상이어야함 SELECT tiv_2015 , count(*) as tot FROM insuran..