aggregation | brain

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 05xx ›

LeetCode 550: Game Play Analysis IV

Problem Restatement We are given a table named Activity . Each row records one player's login activity on one date. We need to report the fraction of players who logged in again on the day immediately after their first login date. The final answer must be rounded to 2 decimal places. The result has one column: fraction The task is to count players with day-two retention, then divide by the...

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

LeetCode 597: Friend Requests I: Overall Acceptance Rate

Problem Restatement We are given two tables: FriendRequest and RequestAccepted . FriendRequest records friend requests sent from one user to another. RequestAccepted records accepted friend requests. We need to compute the overall acceptance rate: number of unique accepted requests / number of unique sent requests The result should be rounded to two decimal places and returned as accept_rate . Important details: Duplicate requests between the same sender and receiver count...

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 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 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 534: Game Play Analysis III

Problem Restatement We are given a table named Activity . Each row records one player's activity on one date. It contains the player id, device id, event date, and number of games played. We need to report, for each player and each date, the total number of games the player has played so far up to that date. The output should contain: player_id event_date games_played_so_far The result may be returned...

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 579: Find Cumulative Salary of an Employee

Problem Restatement We are given an Employee table that stores each employee's salary for different months. For each employee and each month they worked, we need to calculate the sum of that month's salary and the salaries from the previous two months. However, we must not include the employee's most recent month in the output. The result should be ordered by id in ascending order, then by month in descending...

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'...

#aggregation

LeetCode 619: Biggest Single Number

LeetCode 550: Game Play Analysis IV

LeetCode 597: Friend Requests I: Overall Acceptance Rate

LeetCode 596: Classes With at Least 5 Students

LeetCode 512: Game Play Analysis II

LeetCode 511: Game Play Analysis I

LeetCode 586: Customer Placing the Largest Number of Orders

LeetCode 585: Investments in 2016

LeetCode 534: Game Play Analysis III

LeetCode 580: Count Student Number in Departments

LeetCode 579: Find Cumulative Salary of an Employee

LeetCode 578: Get Highest Answer Rate Question

LeetCode 262: Trips and Users