tag

#group-by

17 pages tagged

Practice › LeetCode › LeetCode (archived) › LeetCode 06xx ›

LeetCode 619: Biggest Single Number

Problem Restatement We are given a table MyNumbers . Each row contains a single integer. Column Type Meaning num int A number We need to find the largest number that appears exactly once in the table. If no such number exists, return NULL . The official problem asks for the largest single occurrence number from the table. Input and Output Input table: MyNumbers Output column: num Output rules: Condition Output...

Practice › LeetCode › LeetCode (archived) › LeetCode 06xx ›

LeetCode 618: Students Report By Geography

Problem Restatement We are given a table Student containing names and their geography category. Each row has: Column Type Meaning name varchar Student name continent varchar Continent name We need to transform the table so that each continent becomes a column. Each column should list students from that continent in alphabetical order. If one continent has fewer students than another, missing values should be filled with NULL . The result...

Practice › LeetCode › LeetCode (archived) › LeetCode 06xx ›

LeetCode 615: Average Salary: Departments VS Company

Problem Restatement We are given two tables: Salary and Employee . The Salary table stores each employee's salary for a month. Column Type Meaning id int Salary record id employee_id int Employee id amount int Salary amount pay_date date Salary payment date The Employee table stores each employee's department. Column Type Meaning employee_id int Employee id department_id int Department id We need to compare each department's average salary with the...

Practice › LeetCode › LeetCode (archived) › LeetCode 06xx ›

LeetCode 614: Second Degree Follower

Problem Restatement We are given a table called Follow . Each row means one user follows another user. Column Type Meaning followee varchar The user being followed follower varchar The user who follows followee The pair (followee, follower) is the primary key. A second-degree follower is a user who: follows at least one user is followed by at least one user We need to return every second-degree follower and the...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 574: Winning Candidate

Problem Restatement We are given two tables. Candidate : Column Type Meaning id int Candidate id name varchar Candidate name Vote : Column Type Meaning id int Vote id candidateId int The candidate who received this vote We need to report the name of the candidate who received the largest number of votes. The test cases guarantee exactly one winner. The result table can be returned in any order because...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 596: Classes With at Least 5 Students

Problem Restatement We are given a table called Courses . Each row shows that one student is enrolled in one class. We need to return all classes that have at least five students. The result may be returned in any order. The official table has two columns: student and class . Table Courses Column Type Meaning student varchar Student name class varchar Class name The pair (student, class) is the...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 570: Managers with at Least 5 Direct Reports

Problem Restatement We are given an Employee table. Column Type Meaning id int Employee id name varchar Employee name department varchar Department name managerId int Id of this employee's manager If managerId is NULL , the employee does not report to anyone. We need to find managers who have at least five direct reports. A direct report means an employee whose managerId is equal to that manager's id . Return...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 512: Game Play Analysis II

Problem Restatement We are given an Activity table. Each row records one player login on one date, including the device used and the number of games played. We need to report the device that each player used on their first login date. The result can be returned in any order. The table has columns player_id , device_id , event_date , and games_played ; (player_id, event_date) is the primary key. The...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 511: Game Play Analysis I

Problem Restatement We are given an Activity table. Each row records one player login on one date, together with the device used and the number of games played. We need to report the first login date for each player. The result can be returned in any order. The table has columns player_id , device_id , event_date , and games_played , and the goal is to find the earliest event_date for...

Practice › LeetCode › LeetCode (archived) › LeetCode 06xx ›

LeetCode 602: Friend Requests II: Who Has the Most Friends

Problem Restatement We are given a table called RequestAccepted . Each row means one friend request was accepted. Column Type Meaning requester_id int The user who sent the friend request accepter_id int The user who accepted the friend request accept_date date The date when the request was accepted We need to find the user who has the most friends and return: Column Meaning id The user id num The number...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 586: Customer Placing the Largest Number of Orders

Problem Restatement We are given an Orders table. Each row represents one order and the customer who placed it. We need to return the customer_number of the customer who placed the largest number of orders. The test cases guarantee that exactly one customer has more orders than every other customer. The table has order_number as the primary key and customer_number as the customer ID. Table Orders Column Type Meaning order_number...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 585: Investments in 2016

Problem Restatement We are given an Insurance table. Each row represents one policyholder and includes: Their policy ID. Their total investment value in 2015. Their total investment value in 2016. Their location as latitude and longitude. We need to report the sum of tiv_2016 for policyholders who satisfy both conditions: Their tiv_2015 value is shared by at least one other policyholder. Their (lat, lon) location is unique in the table....

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 580: Count Student Number in Departments

Problem Restatement We are given two tables: Student and Department . Each student belongs to one department. We need to report every department name and the number of students majoring in that department. Departments with no students must still appear in the result with count 0 . The result should be ordered by student_number in descending order. If two departments have the same number of students, order them by dept_name...

Practice › LeetCode › LeetCode (archived) › LeetCode 05xx ›

LeetCode 578: Get Highest Answer Rate Question

Problem Restatement We are given a table called SurveyLog . Each row records one user action on one question. The action can be: 'show' 'answer' 'skip' The answer rate of a question is: number of answer actions / number of show actions We need to return the question_id with the highest answer rate. If multiple questions have the same highest answer rate, return the smallest question_id . The returned column...

Practice › LeetCode › LeetCode (archived) › LeetCode 02xx ›

LeetCode 262: Trips and Users

Problem Restatement We are given two tables: Trips and Users . Trips stores taxi trip requests. Users stores user information, including whether a user is banned. We need to find the cancellation rate for each day from: '2013-10-01' to: '2013-10-03' Only trips where both the client and driver are unbanned should be counted. The cancellation rate is: cancelled requests / total requests Cancelled means the trip status is either: 'cancelled_by_driver'...

Practice › LeetCode › LeetCode (archived) › LeetCode 01xx ›

LeetCode 184: Department Highest Salary

Problem Restatement We are given two tables: Employee and Department . Employee stores employee information. Column Type id int name varchar salary int departmentId int Department stores department information. Column Type id int name varchar Each employee belongs to one department through Employee.departmentId . We need to find employees who have the highest salary in each department. If multiple employees share the highest salary in the same department, we return...

Practice › LeetCode › LeetCode (archived) › LeetCode 01xx ›

LeetCode 182: Duplicate Emails

Problem Restatement We are given a table named Person . Column Type id int email varchar The id column is the primary key. Each row stores one email address. The email field is guaranteed to be non-null. We need to report all duplicate emails. A duplicate email means an email value that appears more than once in the table. The result can be returned in any order. The official statement...