오늘도 배운다

문제 source: LeetCode Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold. Return the resulting table in any order. 제출답안(MySQL) SELECT product_id, year AS first_year, quantity, price FROM sales WHERE (product_id, year) IN (SELECT product_id, MIN(year) as first_year FROM sales GROUP BY product_id) 문제 바로가기(MySQL)

문제 source: LeetCode Write an SQL query to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who: - have the same tiv_2015 value as one or more other policyholders, and - are not located in the same city like any other policyholder (i.e., the (lat, lon) attribute pairs must be unique). Round tiv_2016 to two decimal places. The query result format is in the foll..

문제 source: LeetCode Write an SQL query to report the managers with at least five direct reports. Return the result table in any order. The query result format is in the following example. 제출답안(MS SQL Server) WITH cnt_mng_by_name AS (SELECT managerId AS id, COUNT(id) AS cnt_sub FROM employee GROUP BY managerId) SELECT name FROM cnt_mng_by_name AS cnt_mng JOIN employee AS emp ON cnt_mng.id = emp.i..

문제 source: LeetCode A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write an SQL query to find the employees who are high earners in each of the departments. Return the result table in any order. 제출답안(MySQL) WITH emp_sa..

문제 source: LeetCode Write an SQL query to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each. Return the result table ordered by visit_date in ascending order. 제출답안(MySQL) WITH std AS ( SELECT id, visit_date, people, id - ROW_NUMBER() OVER(ORDER BY id) AS row_num FROM stadium WHERE people >= 100 ), std_cons AS ( SE..

문제 source: LeetCode The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day. Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Ra..

문제 source: LeetCode Write an SQL query to report the names of all the salespersons who did not have any orders related to the company with the name "RED". Return the result table in any order. 제출답안(MySQL) WITH red_sp AS ( SELECT sales_id FROM orders WHERE com_id = (SELECT com_id FROM company WHERE name ='RED') ) SELECT name FROM salesperson WHERE sales_id NOT IN (SELECT sales_id FROM red_sp) 문제 ..

문제 source: LeetCode Write an SQL query to report 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. 제출답안(MySQL) SELECT patient_id, patient_name, conditions FROM patients WHERE conditions REGEXP'^DIAB1|.* DIAB1|DIAB1$' 문제 바로가기(MySQL)