ror_coding

기존 코드도 좋지만, window function의 연산 범위를 잘 익혀야 한다! Question You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).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 the result table ordered by visited_on in ..

문제를 잘 읽자!!! subquery를 잘 활용하자..! Question Write a solution 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.The result format is in the following example. Code select name a..

row_number() 로 홀짝을 구별하여 id에서 빼거나 더한 값을 id로 부여합니다. 따라서 이름은 고정, id만 바뀜. Question Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.Return the result table ordered by id in ascending order.The result format is in the following example. Code select row_number() over(order by if(id % 2 = 0, id - 1, id..

출력해야 하는 항목을 고정시킬 때, union all과 select 을 써서 left/right join 을 해야합니다. Question Write a solution to calculate the number of bank accounts for 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 ..

case when 으로 confirmed 일 때만 count 합니다. Question 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 a solution to find the confirmation rate of each user.Return the result tab..

limit 1 offset 1; Question Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).The result format is in the following example. Table: Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the ..