오늘도 배운다

문제 source: LeetCode Write an SQL query to: - 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. 제출답안(MS SQL Server) WITH rating_count_by_user AS (SELECT user_id, COUNT(ratin..

문제 source: LeetCode Write an SQL query to report the number of bank accounts of each salary category. The salary categories are: "Low Salary": All the salaries strictly less than $20000."Average Salary": All the salaries in the inclusive range [$20000, $50000]."High Salary": All the salaries strictly greater than $50000. The result table must contain all three categories. If there are no account..

문제 source: LeetCode The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places. Write an SQL query to find the confirmation rate of each user. Return the result table in any order. 제출답안(MS..

문제 source: LeetCode You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day). Write an SQL query to compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places. Return result table ordered by visited_on in ascending order..

문제 source: LeetCode Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players. 제출답안(MS SQL Server) WITH log_..

문제 source: LeetCode Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount. Return the result table in any order. 제출답안(MS SQL Server) WITH trxs AS( SELECT id, country, amount, trans_date, FORMAT(trans_date, 'yyyy-MM') AS year_month, CASE WHEN state LIKE 'approved' THEN 1 ELSE 0 END is_ap..

문제 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..