sql | brain

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

LeetCode 197: Rising Temperature

Problem Restatement We are given a table named Weather . Schema: Column Type id int recordDate date temperature int The id column is unique. There are no duplicate recordDate values. We need to find the id of every date where the temperature is higher than the temperature on the previous day. Example table: id recordDate temperature 1 2015-01-01 10 2 2015-01-02 25 3 2015-01-03 20 4 2015-01-04 30 Expected output:...

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

LeetCode 196: Delete Duplicate Emails

Problem Restatement We are given a table named Person . Schema: Column Type id int email varchar The id column is unique. Some rows may contain duplicate email addresses. We need to delete duplicate rows so that: Every email appears only once. The row with the smallest id is kept. All other duplicate rows are removed. Example table: id email 1 john@example.com 2 bob@example.com 3 john@example.com After deletion: id email...

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

LeetCode 175: Combine Two Tables

Problem Restatement We are given two tables: Person and Address . The Person table stores each person's ID, first name, and last name. The Address table stores address records. Each address belongs to a person through personId . We need to report this information for every person: firstName, lastName, city, state The important part is that every person must appear in the result, even if that person has no address....

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

LeetCode 620: Not Boring Movies

Problem Restatement We are given a table Cinema . Each row represents a movie. Column Type Meaning id int Movie id movie varchar Movie name description varchar Movie description rating float Movie rating We need to: Select movies with odd id values Exclude movies whose description is "boring" Sort the result by rating in descending order The official problem asks for filtering movies with odd IDs and non-boring descriptions, ordered...

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

LeetCode 613: Shortest Distance in a Line

Problem Restatement We are given a table called Point . Each row stores one point on the X-axis. Column Type Meaning x int Position of the point on the X-axis The column x is the primary key, so every point is unique. We need to find the shortest distance between any two points. The official problem asks for the minimum distance between any two points from the Point table. Input...

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

LeetCode 612: Shortest Distance in a Plane

Problem Restatement We are given a table called Point2D . Each row represents one point in a 2D plane. Column Type Meaning x decimal x-coordinate y decimal y-coordinate We need to find the shortest Euclidean distance between any two distinct points. The result should contain one column: Column Meaning shortest Minimum distance between any pair of points The final answer should be rounded to two decimal places. The official problem...

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

LeetCode 610: Triangle Judgement

Problem Restatement We are given a table called Triangle . Each row contains three integer side lengths: Column Type Meaning x int First side length y int Second side length z int Third side length For each row, we need to report whether the three side lengths can form a valid triangle. The result should contain the original side lengths and a new column called triangle , whose value is...

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

LeetCode 608: Tree Node

Problem Restatement We are given a table called Tree . Each row represents one node in a binary tree. Column Type Meaning id int Node id p_id int Parent node id We need to classify every node into one of three types: Type Meaning Root The node has no parent Leaf The node has no children Inner The node has both a parent and at least one child The result...

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

LeetCode 607: Sales Person

Problem Restatement We are given three tables: SalesPerson , Company , and Orders . The SalesPerson table stores salesperson information. Column Type Meaning sales_id int Salesperson id name varchar Salesperson name salary int Salary commission_rate int Commission rate hire_date date Hire date The Company table stores company information. Column Type Meaning com_id int Company id name varchar Company name city varchar Company city The Orders table stores order information. Column...

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 571: Find Median Given Frequency of Numbers

Problem Restatement We are given a Numbers table. Column Type Meaning num int The number value frequency int How many times this number appears Each row stores a number and its frequency. Conceptually, the table represents a decompressed list of numbers. For example, if num = 0 and frequency = 7 , then 0 appears seven times in the full list. We need to report the median of all numbers...

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 595: Big Countries

Problem Restatement We are given a table called World . Each row contains information about one country. A country is considered big if at least one of these conditions is true: Its area is at least 3,000,000 . Its population is at least 25,000,000 . We need to return the name , population , and area of all big countries. The result may be returned in any order. Table World...

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

LeetCode 569: Median Employee Salary

Problem Restatement We are given an Employee table. Column Type Meaning id int Employee id company varchar Company name salary int Employee salary We need to return the rows that contain the median salary of each company. When sorting salaries inside a company, ties are broken by id . If a company has an odd number of employees, return the one middle row. If a company has an even number...

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 603: Consecutive Available Seats

Problem Restatement We are given a table called Cinema . Each row tells us whether a seat is free or occupied. Column Type Meaning seat_id int Seat id free bool 1 means free, 0 means occupied We need to return all free seats that are part of at least one consecutive pair of free seats. The result should be ordered by seat_id . The problem note says seat_id is auto-incremented,...

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

LeetCode 627: Swap Salary

Problem Restatement We are given a table named Salary . Each row stores information about one employee. The sex column contains only two possible values: Value Meaning m male f female We need to update the table so that every m becomes f , and every f becomes m . The problem requires one UPDATE statement. We should not use a temporary table, and we should not write a SELECT...

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

LeetCode 626: Exchange Seats

Problem Restatement We are given a table named Seat . Each row stores a student and their seat id . The id values start from 1 and increase continuously. We need to swap every two adjacent students: Original Seat New Seat 1 gets student from 2 2 gets student from 1 3 gets student from 4 4 gets student from 3 If the number of students is odd, the last...

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

LeetCode 601: Human Traffic of Stadium

Problem Restatement We are given a table called Stadium . Each row records one stadium visit day. The table has three columns: Column Type Meaning id int Visit id visit_date date Date of the visit people int Number of people who visited We need to return all rows that are part of a group of at least three consecutive id values where every row has people >= 100 . The...

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 584: Find Customer Referee

Problem Restatement We are given a table called Customer . Each row stores a customer, their name, and the ID of the customer who referred them. We need to return the names of customers who were not referred by the customer with id = 2 . That includes two kinds of customers: Customers whose referee_id is not 2 . Customers whose referee_id is NULL , meaning they were not referred...

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

LeetCode 577: Employee Bonus

Problem Restatement We are given two tables: Employee and Bonus . The task is to report each employee's name and bonus when the employee satisfies one of these conditions: The employee has a bonus less than 1000 . The employee has no bonus record. Return the result table in any order. The official problem describes this as reporting the name and bonus amount of each employee whose bonus is less...

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 185: Department Top Three Salaries

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 a department through Employee.departmentId . We need to find employees who are high earners in their department. A high earner is an employee whose salary is in the top three unique...

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 183: Customers Who Never Order

Problem Restatement We are given two tables: Customers and Orders . Customers stores customer information. Column Type id int name varchar Orders stores order information. Column Type id int customerId int The customerId column in Orders refers to Customers.id . We need to find all customers who never placed an order. The result can be returned in any order. The official problem asks for all customers who never order anything....

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

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

LeetCode 181: Employees Earning More Than Their Managers

Problem Restatement We are given a table named Employee . Column Type id int name varchar salary int managerId int Each row stores one employee. The managerId column points to the id of that employee's manager. We need to return the names of employees who earn more than their managers. The result can be returned in any order. The official problem asks for employees whose salary is greater than their...

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

LeetCode 180: Consecutive Numbers

Problem Restatement We are given a table named Logs . Column Type id int num varchar The column id is the primary key. It is an auto-increment column starting from 1 . We need to find every number that appears at least three times consecutively. The result can be returned in any order. The output column should be named ConsecutiveNums . The official statement asks for all numbers that appear...

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

LeetCode 178: Rank Scores

Problem Restatement We are given a table named Scores . Column Type id int score decimal Each row stores the score of a game. We need to rank all scores from highest to lowest. The ranking rules are: Rule Meaning Higher score gets smaller rank The highest score has rank 1 Equal scores share the same rank Two 4.00 scores both get rank 1 No rank gaps After rank 1...

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

LeetCode 177: Nth Highest Salary

Problem Restatement We are given a table named Employee . Column Type id int salary int Each row stores one employee and that employee's salary. We need to write a SQL function that returns the n th highest distinct salary. If there are fewer than n distinct salaries, the function should return null . The official problem asks for the n th highest distinct salary from Employee , returning null...

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

LeetCode 176: Second Highest Salary

Problem Restatement We are given a table named Employee . Column Type id int salary int Each row stores one employee and their salary. We need to return the second highest distinct salary. The word distinct matters. If two employees both have salary 300 , that salary still counts as one salary level. If there is no second highest distinct salary, we return null . Input and Output Item Meaning...

#sql

LeetCode 197: Rising Temperature

LeetCode 196: Delete Duplicate Emails

LeetCode 175: Combine Two Tables

LeetCode 620: Not Boring Movies

LeetCode 619: Biggest Single Number

LeetCode 618: Students Report By Geography

LeetCode 615: Average Salary: Departments VS Company

LeetCode 614: Second Degree Follower

LeetCode 613: Shortest Distance in a Line

LeetCode 612: Shortest Distance in a Plane

LeetCode 610: Triangle Judgement

LeetCode 550: Game Play Analysis IV

LeetCode 608: Tree Node

LeetCode 574: Winning Candidate

LeetCode 607: Sales Person

LeetCode 597: Friend Requests I: Overall Acceptance Rate

LeetCode 596: Classes With at Least 5 Students

LeetCode 571: Find Median Given Frequency of Numbers

LeetCode 570: Managers with at Least 5 Direct Reports

LeetCode 595: Big Countries

LeetCode 569: Median Employee Salary

LeetCode 512: Game Play Analysis II

LeetCode 511: Game Play Analysis I

LeetCode 603: Consecutive Available Seats

LeetCode 627: Swap Salary

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

LeetCode 586: Customer Placing the Largest Number of Orders

LeetCode 626: Exchange Seats

LeetCode 601: Human Traffic of Stadium

LeetCode 585: Investments in 2016

LeetCode 534: Game Play Analysis III

LeetCode 584: Find Customer Referee

LeetCode 580: Count Student Number in Departments

LeetCode 579: Find Cumulative Salary of an Employee

LeetCode 578: Get Highest Answer Rate Question

LeetCode 577: Employee Bonus

LeetCode 262: Trips and Users

LeetCode 185: Department Top Three Salaries

LeetCode 184: Department Highest Salary

LeetCode 183: Customers Who Never Order

LeetCode 182: Duplicate Emails

LeetCode 181: Employees Earning More Than Their Managers

LeetCode 180: Consecutive Numbers

LeetCode 178: Rank Scores

LeetCode 177: Nth Highest Salary

LeetCode 176: Second Highest Salary