오늘도 배운다

문제 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 There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board. Write an SQL query to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit. 제출답안(MS SQL Se..

문제 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 If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled. The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order. Write an SQL query to find the percentage of immediate orders in the first..

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