0% found this document useful (0 votes)
1K views122 pagesLeet Code Solution
Uploaded by
Mahesh GullaCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
1K views122 pagesLeet Code Solution
Uploaded by
Mahesh GullaCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 122
LeetCode SQL Problem Solving
Questions With Solutions
LeetCode
SQL Solutions
175. Combine Two Tables | Easy | LeetCode
Table: PersonTable: address
| Column Name | Type |
| Addresstd | int |
| PersoniId | int |
| city | varchar |
| state | varchar |
AddressId is the primary key colunn for this table.
Write a SQL query for a report that provides the following information for each
person in the Person table, regardless if there is an address for each of those
people:
FirstName, LastName, City, State
Solution
SELECT p.FirstName, p.LastName, a.City, a.State
FROM Person p
LEFT JOIN Address a
ON p.PersonId = a.PersonId;
176. Second Highest Salary | Easy | LeetCode
Write a SQL query to get the second highest salary from the Enployee table.For example, given the above Employee table, the query should return 220 as the
second highest salary. If there is no second highest salary, then the query should
return null.
Solution
SELECT Max(Salary) SecondHighestSalary
FROM Employee WHERE Salary M.Salary;
182. Duplicate Emails | Easy | LeetCode
Write a SQL query to find all duplicate emails in a table named Person.
| td | email |
| [email protected] |
la
1.2. | [email protected] |
[3
| [email protected] |
For example, your query should return the following for the above table:
| email |
| [email protected] |
Note: All emails are in lowercase.
Solution
SELECT Email
FROM Person
GROUP BY Email
HAVING count(*) > 1.WITH CTE AS(
SELECT Email, ROW_NUMBER() OVER(PARTITION BY Email ORDER BY Email) AS RN
FROM Person
SELECT €mail
FROM CTE
WHERE RN > 15
183. Customers Who Never Order | Easy | LeetCode
Suppose that a website contains two tables, the custoners table and the orders
table. Write a SQL query to find all customers who never order anything.
Table: Customers .
Table: orders.
| 1d | customerra |
fa 13 |
f2 42 |Using the above tables as example, return the following:
Solution
SELECT Name AS Custoners
FROM Customers
LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE CustomerId IS NULL;
SELECT Name as Customers
FROM Customers
WHERE Id NOT INC
SELECT CustonerId
FROM Orders
184. Department Highest Salary | Medium | Lee(Code
The Enployee table holds all employees. Every employee has an Id, a salary, and
there is also a column for the department Id.| Id | Name | Salary | Departmentid |
].1 [Joe | 7eeea | 4 |
[2 | aim | 9ee0@ | 2 |
[3 | Henry | seaea | 2 |
| 4 | sam | 6ee0e | 2 |
[5 [max | 90000 | 4 |
Write a SQL query to find employees who have the highest salary in each of the
departments. For the above tables, your SQL query should return the following rows
(order of rows does not matter).
| Department | Employee | Salary |
|r | Max | seeee |
[ar | 34m | see00 |
| sales | Henry | se0ee |
Explanation:
Max and Jim both have the highest salary in the IT department and Henry has thehighest salary in the Sales department.
Solution
SELECT Department.Name AS Department, Employee.Name AS Employee, Salary
FROM Employee
JOIN Department
ON Employee.DepartmentId = Department.1d
WHERE (DepartmentId, Salary) IN(
SELECT Departmentid, MAX(Salary) AS Salary
FROM Employee
GROUP BY DepartmentId
3
185. Department Top Three Salaries
Hard | LeetCode
The Employee table holds all employees. Every employee has an Id, and there is also
a column for the department Id.
| 1d | Name” | Salary | Departmentid |
Joe | 85000
Henry | 89000
sam | 69000
Janet | 69000
Randy | 85000
will | 70000
Ia
12
13
14
[5
6
\7
| |
| |
| |
| max | 90000 |
| |
| |
| |
The vepartnent table holds all departments of the company.Write a SQL query to find employees who earn the top three salaries in each of the
department. For the above tables, your SQL query should return the following rows
(order of rows does not matter).
| Department | Employee | Salary
sales
sales
Explanation:
In IT department, Max earns the highest salary, both Randy and Joe earn the
second highest salary, and Will earns the third highest salary. There are only two
employees in the Sales department, Henry earns the highest salary while Sam earns
the second highest salary.
Solution
WITH department_ranking AS (
SELECT Name AS Enployee, Salary ,DepartmentId
,DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rnk
FROM EmployeeSELECT d.Name AS Department, r.Enployee, r.Salary
FROM department_ranking AS r
JOIN Department AS d
ON r.DepartmentId = d.1d
WHERE r.pnk 25).
In 2015-01-04, temperature was higher than the previous day (20 -> 30).
Solution
SELECT t.1d
FROM Weather AS t, Weather AS y
WHERE DATEDIFF(t.RecordDate, y.RecordDate) = 1
AND t.Tenperature > y. Temperature;
SELECT t.1d
FROM Weather t
JOIN Weather y
ON DATEDIFF(t.recordbate, y.recordDate) = 1 AND
tetemperature > y.temperatures262. Trips and Users | Hard | LeetCode
Table: Trips
| Column Name | Type |
| ad | ant \
| Client_td | int |
| river_td | int \
| city_td | int |
| status | enum |
| Request_at | date |
Id is the primary key for this table.
The table holds all taxi trips. Each trip has a unique Id, while Client_Id and or
Status is an ENUM type of (‘completed’, ‘cancelled_by driver’, ‘cancelled by_clie
—
Table: Users
| Colunn Nare | Type |
+
| Userstd | int \
| Banned | enum |
| Role | enum |
Users_Id is the primary key for this table.
‘The table holds all users. Each user has a unique Users_Id, and Role is an ENUM t
Status is an ENUM type of (“Yes’, No’).
—
Write a SQL query to find the cancellation rate of requests with unbanned users
(both client and driver must not be banned) each day between "2013-10-01" and
"2013-10-03".The cancellation rate is computed by dividing the number of canceled (by client or
driver) requests with unbanned users by the total number of requests with
unbanned users on that day.
Return the result table in any order. Round Cancellation Rate to two decimal points.
The query result format is in the following example:
rer
Trips table
| Id | Client_td | Driver_td | City Id | status | Request_at |
pada | 10 [a | completed | 2013-10-01 |
12 12 Jaa [a | cancelled_by_driver | 2013-10-01 |
13 13 | a2 le | completed | 2013-10-01 |
lala [33 lé | cancelled_by_client | 2013-10-01 |
[sya | 16 Ja | completed | 2013-10-02 |
16 12 ja Is | completed | 2013-10-02 |
17 13 | a2 le | completed | 2013-10-02 |
|a |2 | 12 [2 | completed | 2013-10-03 |
1913 | 10 | a2 | completed | 2013-10-03 |
19] 4 [33 [2 | cancelled_by_driver | 2013-10-03 |
Users table
| Users_td | Banned | Role |
fa [No | client |
12 | yes | client |
13 | No | client |
l4 | No | client |
| 10 [No | driver |
Jaa [No | driver |
| a2 [No | driver |
[3 [No | driver |
Result table:| bay | Cancellation Rate |
| 2013-10-21 | 0.33 |
| 2013-10-02 | 0.08 \
| 2013-10-23 | 0.50 |
on 2013-10-21:
- There were 4 requests in total, 2 of which were canceled.
- However, the request with Id=2 was made by a banned client (User_Id=2), so it i
- Hence there are 3 unbanned requests in total, 1 of which was canceled.
~ The Cancellation Rate is (1 / 3) = 0.33
on 2013-10-02:
- There were 3 requests in total, @ of which were canceled.
- The request with Id=6 was made by a banned client, so it is ignored.
- Hence there are 2 unbanned requests in total, @ of which were canceled.
- The Cancellation Rate is (@ / 2) = 0.00
On 2013-10-03:
- There were 3 requests in total, 1 of which was canceled.
- The request with Id=8 was made by a banned client, so it is ignored.
- Hence there are 2 unbanned request in total, 1 of which were canceled.
~ The Cancellation Rate is (1 / 2) = 0.50
Solution
SELECT Request_at AS Day,
ROUND(SUM(IF(Status"completed", 1, @))/COUNT(Status),2) AS "Cancellation Rate”
FROM Trips
WHERE Request_at BETWEEN "2013-10-01" AND "2013-10-03"
AND Client_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
AND Driver_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;511. Game Play Analysis I | Easy | {@ LeetCode
Table: activity
| Column Name | Type |
| player id | int |
| deviceid | int |
J event_date | date |
| games_played | int I
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (poss
—,,
Write an SQL query that reports the first login date for each player.
The query result format is in the following example:
ter
Activity table:
| player_id | device_id | event_date | games_played |
ja | 2 | 2016-03-01 | 5 \
Ia l2 | 2016-05-02 | 6 \
|2 13 | 2017-06-25 | 2 \
13 fa | 2016-03-02 | @ |
13 [4 | 2018-07-03 | 5 |
Result table:
| player_id | first_login |
ia | 2016-03-01 |2 | 2017-06-25 |
3 | 2016-03-02 |
Solution
SELECT player_id, MIN(event_date) as first_login
FROM Activity
GROUP BY player_id
512. Game Play Analysis Il | Easy | @ LeetCode
Table: activity
| Column Name | Type |
| playerid | int |
| device id | int |
J event_date | date |
| ganes_played | int |
e
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (poss
————
Write a SQL query that reports the device that is first logged in for each player.
The query result format is in the following example:Activity table:
| player_id | device_id | event_date | games_played |
la 12 | 2016-03-01 | 5 |
[a |2 | 2016-05-02 | 6 \
[2 13 | 2017-06-25 | 1 |
13 Ja | 2016-03-02 | 0 \
13 14 | 2018-07-03 | 5 |
device_id |
Solution
SELECT DISTINCT player_
FROM Activity
d, device id
WHERE (player_id, event_date) in (
SELECT player_id, min(event_date)
FROM Activity
GROUP BY player_id)
SELECT a.player_id, b.device_id
FROM
(SELECT player_id, MIN(event_date) AS event_date FROM Activity
GROUP BY player_id) a
3OIN Activity bON a.player_id = b.player_id AND a.event_date = b.event_date;
SELECT player_id, device_id
FROM
(SELECT player_id, device_id, event_date,
ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS r
FROM Activity) lookup
WHERE r = 4;
534. Game Play Analysis III | Medium | (@ LeetCode
Table: activity
| column Nae | Type |
| playerid | int |
| deviceid | int |
| event_date | date |
| games_played | int |
(player_id, event_date) is the primary key of this table
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (poss
—
Write an SQL query that reports for each player and date, how many games played
so far by the player. That is, the total number of games played by the player until
that date. Check the example for clarity.
The query result format is in the following example:
Activity table:| player_id | device_id | event_date | games_played |
Ja [2 | 2016-03-01 | 5 \
fa |2 | 2016-05-02 | 6 \
[a 13 | 2017-06-25 | 1 \
13 [a | 2016-03-02 | @ \
13 [4 | 2018-07-03 | 5 |
| player_id | event_date | games_played_so_far |
| 2016-03-01 | 5 |
| 2o16-05-02 | 11 |
| 2017-06-25 | 12 |
| 2016-03-02 | @ |
| 2e18-07-e3 | 5 |
la
la
fa
13
13
For the player with id 1, 5 + 6 = 11 games played by 2016-05-02, and 5 +6 +1 =
For the player with id 3, @ + 5 = 5 games played by 2018-07-03.
Note that for each player we only care about the days when the player logged in.
—
Solution
SELECT t1.player_id, tl.event_date, SUM(t2.games_played) as games_played_so_far
FROM Activity ti
JOIN Activity #2
ON ti.player_id = t2.player_id
WHERE t1.event_date >= t2.event_date
GROUP BY t1.player_id, t1.event_date;
SELECT player_id, event_date,‘SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_play
FROM Activity;
550. Game Play Analysis IV | Medium | (@ LeetCode
Table: Activity
| Column Name | Type |
| playerid | int |
| deviceid | int |
J event_date | date |
| games_played | int I
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (poss
XX
Write an SQL query that reports 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.
The query result format is in the following example:
Activity table:
| player_id | device_id | event_date | games_played |
| 2016-23-01
fa 12 Is
fa l2 | 2016-03-02 | 6 \
12 13 | 2017-06-25 | 11 | 2016-03-02 | 0 \
4 | 2018-07-03 | 5 |
Only the player with id 1 logged back in after the first day he had logged in so
——T—TT.
Solution
SELECT ROUND(sum(CASE WHEN t1.event_date = t2.first_event+1 THEN 1 ELSE @ END)/CO
FROM Activity t1
JOIN
(SELECT player_id, MIN(event_date) AS first_event
FROM Activity
GROUP BY player_id) t2
ON t1.player_id = t2.player_id;
SELECT ROUND(COUNT (DISTINCT b.player_id)/COUNT(DISTINCT a.player_id),2) AS fracti
FROM
(SELECT player_id, MIN(event_date) AS event_date FROM Activity
GROUP BY player_id) a
LEFT JOIN Activity b
ON a.player_id = b.player_id AND a.event_date+1 = b.event_date;
—
569. Median Employee Salary | Hard | ( LeetCodeThe Employee table holds all employees. The employee table has three columns:
Employee Id, Company Name, and Salary.
[td | Company — | Salary |
In oda | 2341 |
2 1A [3a1 |
131A fas
I4oda | as314 |
Is 1A last |
Is 1a [513 |
7 18 fas |
Is 18 3 |
Is 18 | aasa |
jw 18 [3345 |
Iu [8 Jaz |
2 18 | 2340 |
Ii | 2345 |
Iu dc | 2645 |
fas fc | 2645 |
le | c | 2652 |
lz dc les |
Write a SQL query to find the median salary of each company. Bonus points if you
can solve it without using any built-in SQL functions.
[td | Company | Salary |
|9
| | |
| | |
jz | 8 | 234 |
| | Il
laa | | |Solution
SELECT t1.Id AS Id, t1.Company, ti.Salary
FROM Employee AS t1 JOIN Employee AS t2
ON t1.Company = t2.Company
GROUP BY t1.Id
HAVING abs(sum(CASE WHEN t2.Salaryct1.Salary THEN 1
WHEN t2.Salary>t1.Salary THEN -1
WHEN t2.Salary=ti.Salary AND t2.Idct1.Id THEN 1
WHEN t2.Salary=ti.Salary AND t2.Id>ti.Id THEN -1
ELSE @ END)) = 5)
@ LeetCode
571. Find Median Given Frequency of Numbers
The Nunbers table keeps the value of number and its frequency.In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) /
2=0.
| median |
=
| 0.0000 |
Write a query to find the median of all numbers and name the result as median.
Solution
SELECT avg(t3.Number) as median
FROM Nunbers as t3
JOIN
(SELECT t1.Nunber,
abs(SUM(CASE WHEN t1.Number>t2.Number THEN t2.Frequency ELSE @ END) -
SUM(CASE WHEN t1.Nunberct2.Nunber THEN t2.Frequency ELSE @ END)) AS
FROM numbers AS ti, numbers AS t2
GROUP BY t1.Nunber) AS ta
ON t3.Number = t4.Number
WHERE t3.Frequency>=t4.count_diff
574. Winning Candidate | Medium | (@ LeetCode
Table: Candidate
| id | Name |
laTable: vote
| id | Candidatetd |
wn wan
1
2
3
4
5
id is the auto-increment primary key, Candidaterd is the id appeared in Candidate
table. Write a sql to find the name of the winning candidate, the above example will
return the winner B.
Notes: You may assume there is no tie, in other words there will be at most one
winning candidate.
SolutionSELECT Name
FROM Candidate
WHERE id = (SELECT Candidaterd
FROM Vote
GROUP BY Candidateld
ORDER BY COUNT(1) desc
LIMIT 1)
577. Employee Bonus | Easy | ( LeetCode
Select all employee's name and bonus whose bonus is = @ AND
a.month - b.month 1)
586. Customer Placing the Largest Number of Orders
@ LeetCode
Query the customer_number from the orders table for the customer who has placed
the largest number of orders.
——TTT3
Easy |
.It is guaranteed that exactly one customer will have placed more orders than any
other customer.
The orders table is defined as follows:
| order_number (Pk) |
custoner_nunber
order_date
required date
shipped_date
status
comment
Sample Input
Type
int
int
date
date
date
char(15)
char(20)
| order_nunber | customer_nunber
Sample Output
| custoner_nunber
I-
13 |
Explanation
order_date
2017-04-08
2017-04-15
2017-04-16
2017-04-18
| required_date |
2017-04-13
2017-04-20
2017-04-25
2017-04-28
shipped_date
2017-04-12 | Cl
2017-04-18 | C1
2017-4-28 | Cl
2017-04-25 | Cl
—‘The customer with number '3' has two orders, which is greater than either custome
So the result is customer_number 3".
Solution
t
SELECT customer_number FROM orders
GROUP BY customer_nunber
ORDER BY COUNT(1) DESC
LIMIT 1
595. Big Countries
Easy | LeetCode
There is a table world| name | continent | area | population | gdp |
| Afghanistan | Asia | 652230 | 255e0100 ©—|_20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 «=| 188681000 =|
| Andorra | Europe | 468 | 78135 | 3712e0e |
| Angola | Africa | 1246708 | 20609284 | 100990000 |
A country is big if it has an area of bigger than 3 million square km or a population of
more than 25 million.
Write a SQL solution to output big countries’ name, population and area.
For example, according to the above table, we should output:
| nane | population | area |
| Afghanistan | 2550100 | 652230 |
| Algeria | 37100000 | 2381742 |
Solution
SELECT name, population, area
FROM World
WHERE area >= 3000002 OR population > 25000000;
596. Classes More Than 5 Students | Easy | LeetCodeThere is a table courses with columns: student and class
Please list out all classes which have more than or equal to 5 students.
For example, the table:
| student | class
math
English
Math
Biology
amon ee
Computer
math
math
I
|
I
|
Math I
|
I
|
|
| Math
Solution
SELECT class
FROM courses
GROUP BY class
HAVING count (DISTINCT Student)>=5597. Friend Requests
LeetCode
: Overall Acceptance Rate | Easy |
In social network like Facebook or Twitter, people send friend requests and accept
others’ requests as well. Now given two tables as below: Table: friend_request
| sender_id | send_to_id |request_date|
Ja 12 | 2016_96-01 |
ja 13 | 2016_06-01 |
Ja [4 | 2016_96-01 |
|2 13 | 2016_06-02 |
13 [4 | 2016-06-09 |
Table: request_accepted
| requester_id | accepter_id |accept_date |
| 2016_96-03
2016-06-08
2016-26-09
1
1
2
3
3
The rows with ids 2 and 3 are not included because we need at least three consecuSELECT DISTINCT s1.*
FROM Stadium s1 JOIN Stadium s2 JOIN Stadium s3
ON (s1.id = s2.id-1 AND st.id = s3.id-2) OR,
(slid = s2.id+1 AND s1.id = s3.id-1) oR
(S1.id = s2.idtd AND st.id = s3.id2)
WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people>=100
ORDER BY visit_date
In social network like Facebook or Twitter, people send friend requests and accept
others’ requests as well. Table request_accepted holds the data of friend
acceptance, while requesterid and accepterid both are the id of a person.
| requester_id | accepter_id | accept_date|
|n-= =
| 2016_26-03 |
| 2016-06-08 |
| 2016-06-08 |
| 2016-06-09 |
Write a query to find the the people who has most friends and the most friends.
number. For the sample data above, the result is:
| id | num |Note:
It is guaranteed there is only 1 people having the most friends. The friend request
could only been accepted once, which mean there is no multiple records with the
same requesterid and accepterid value. Explanation: The person with id ‘3’ is a friend
of people ‘1, '2' and ‘4; so he has 3 friends in total, which is the most number than
any others.
Follow-up: In the real world, multiple people could have the same most number of
friends, can you find all these people in this case?
SELECT t.id, sum(t.num) AS num
FROM (
(SELECT requester_id AS id, COUNT(1) AS num
FROM request_accepted
GROUP BY requester_id)
union all
(SELECT accepter_id AS id, COUNT(1) AS num
FROM request_accepted
GROUP BY accepter_id)) AS t
GROUP BY t.id
ORDER BY num DESC
LIMIT 23
603. Consecutive Available Seats | Easy | (@ LeetCode
Several friends at a cinema ticket office would like to reserve consecutive available
seats. Can you help to query all the consecutive available seats order by the seatid
using the following cinema table?Your query should return the following result for the sample case above.
| seat_id |
I- “I
13 |
14 |
Is |
Note:
The seat_id is an auto increment int, and free is bool ('1’ means free, and ‘0’ means
occupied.). Consecutive available seats are more than 2(inclusive) seats
consecutively available.
Solution
SELECT DISTINCT t1.seat_id
FROM cinema AS t1 JOIN cinena AS t2
ON abs(t1.seat_id-t2.seat_i
WHERE t1.frees'1' AND t2.fre
ORDER BY t1.seat_id
“
607.Sales Person | Easy | ( LeetCode
Description
Given three tables: salesperson, company, orders . Output all the names in the table
salesperson, who didn’t have sales to company ‘RED!
Example InputTable: salesperson
| sales_id | name | salary | conmission_rate | hire_date |
loa | John | 190000 | 6 | 4/1/2006 |
| 2 | amy | 120000 | = 5 | s/2010 |
13 | Mark | 65000 | 12 | 12/25/2008]
|o4 | Pan | 25000 | 25 | 1/1/2005 |
los | Alex | 50000 | 10 | 2/3/2007 |
The table salesperson holds the salesperson information. Every salesperson has a
sales_id and a name. Table: company
| comid | name | city |
| 2 | Re | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
The table company holds the company information. Every company has a com_id
and a name. Table: orders
| order_id | date | com_id | sales_id | amount |
fa laa | 3 | 4 | 188880 |
|2 lara | 4 | 5 | S088 |
13 laa} 1 | 1 | see8e |
l4 42a} 1 | & | 25000 |The table orders holds the sales record information, salesperson and customer
company are represented by salesid and comid. output
Explanation
According to order '3' and ‘4’ in table orders, it is easy to tell only salesperson John’
and ‘Alex’ have sales to company ‘RED; so we need to output all the other names in
table salesperson.
Solution
SELECT name
FROM salesperson
WHERE name NOT IN
(SELECT DISTINCT salesperson.nane
FROM salesperson, orders, company
WHERE company.name = "RED"
AND salesperson.sales_id = orders.sales_id
AND orders.com_id = company.com_id)
608. Tree Node | Medium | (@ LeetCode
Given a table tree, id is identifier of the tree node and p_id is its parent node's id.Each node in the tree can be one of three types:
Leaf: if the node is a leaf node. Root: if the node is the root of the tree. Inner: If the
node is neither a leaf node nor a root node. Write a query to print the node id and
the type of the node. Sort your output by the node id. The result for the above
sample is:
Explanation
Node ‘1’ is root node, because its parent node is NULL and it has child node ‘2’ and
'3! Node ‘2’ is inner node, because it has parent node ‘’ and child node ‘4’ and ‘5:
Node 3; ‘4’ and ‘5’ is Leaf node, because they have parent node and they don't have
child node. And here is the image of the sample tree as below:Note
If there is only one node on the tree, you only need to output its root attributes.
Solution
SELECT tl.id,
CASE
WHEN TSNULL(t2.p_id) THEN ‘Root’
WHEN ISNULL(MAX(t2.id)) THEN ‘Leaf”
ELSE ‘Inner’
END AS Type
FROM tree AS t1 LEFT JOIN tree AS t2
ON th.id = t2.pid
GROUP BY t1.id, t1.p_id
.610. Triangle Judgement | Easy | ( LeetCode
A pupil Tim gets homework to identify whether three line segments could possibly
form a triangle. However, this assignment is very heavy because there are hundreds
of records to calculate. Could you help Tim by writing a query to judge whether
these three sides can form a triangle, assuming table triangle holds the length of the
three sides x, y and z.
Ix ly Iz]
[evse|es |e
| 13 | 15 | 3e |
| 10 | 20 | a5 |
For the sample data above, your query should return the follow result:
|_z | triangle |
I- “I
| 13 | 15 | 30 | No \
| 1@ | 20 | 15 | Yes |
Solution
LT
SELECT x, y, 2,
case
WHEN x+y>z AND y+z>x AND x+z9y THEN Yes"
ELSE 'No"
END AS triangle
FROM triangle
612. Shortest Distance in a Plane | Medium | (@ LeetCodeTable point_2d holds the coordinates (x,y) of some unique points (more than two) in
a plane. Write a query to find the shortest distance between these points rounded to
2 decimals.
The shortest distance is 1.00 from point (-1,-1) to (-1,2). So the output should be:
| shortest |
Note: The longest distance among all the points are less than 10000.
Solution
rr)
SELECT ROUND(MIN(SQRT((t2.x-t2.x)*(tL.x-t2.x) + (tLy-t2.y)*(ELy-t2.y)))s 2) as
FROM point_2d AS t1, point_2d as t2
WHERE tl.x!=t2.x OR tL.yl=t2.y
613. Shortest Distance in a Line | Easy | (@ LeetCodeTable point holds the x coordinate of some points on x-axis in a plane, which are all
integers. Write a query to find the shortest distance between two points in these
points.
The shortest distance is’ obviously, which is from point '-1' to ‘0: So the output is
as below:
| shortest |
I- “I
fa |
Note: Every point is unique, which means there is no duplicates in table point.
Follow-up: What if all these points have an id and are arranged from the left most to
the right most of x axis?
Solution
SELECT tl.x-t2.x AS shortest
FROM point AS ti JOIN point AS t2
WHERE t1.xot2.x
ORDER BY (t1.x-t2.x) ASC
LIMIT 1.
614. Second Degree Follower | Medium | (@j LeetCodeIn facebook, there is a follow table with two columns: followee, follower.
Please write a sql query to get the amount of each follower's follower if he/she has
one.
For example:
| followee | follower |
| follower | num
Explanation: Both B and D exist in the follower list, when as a followee, B's follower is
Cand D, and D's follower is E. A does not exist in follower list.
Note: Followee would not follow himself/herself in all cases. Please display the result
in follower's alphabet order.
SolutionSELECT f1.follower, COUNT(DISTINCT £2.follower) AS nun
FROM follow AS f1 JOIN follow AS #2
ON #1.follower = £2.followee
GROUP BY #1.follower;
615. Average Salary: Departments VS Company | Hard | @
LeetCode
Given two tables as below, write @ query to display the comparison result
{higher/lower/same) of the average salary of employees in a department to the
company’s average salary. Table: salary
employee_id | amount | pay_date
| |
I----1 |
Jaqa | 9200 | 2017-03-31 |
12 |2 | 6ee@ | 2017-03-31 |
13 13 | 1@000 | 2017-03-31 |
l4 1a | 7ee0 | 2017-02-28 |
[5 ]2 | 6200 | 2017-02-28 |
le 13 | sega | 2017-02-28 |
The employeeid column refers to the employeeid in the following table employee.
| employee_id | department_id |So for the sample data above, the result is:
| pay_month | |
“I |
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower \
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |
Explanation In March, the company’s average salary is (9000+6000+10000)/3 =
8333.33... The average salary for department ‘I’ is 9000, which is the salary of
‘employeeid ‘1’ since there is only one employee in this department. So the
comparison result is ‘higher’ since 9000 > 8333.33 obviously. The average salary of
department '2' is (6000 + 10000)/2 = 8000, which is the average of employeeid '2'
and ‘3: So the comparison result is lower’ since 8000 t2.amount THEN "higher"
WHEN t1.amount ‘boring’ AND 1D % 2 = 1
ORDER BY rating DESC;
626. Exchange Seats
Medium | LeetCode
Mary is a teacher in a middle school and she has a table seat storing students’
names and their corresponding seat ids.
The column id is continuous increment.Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?
| id | student |
+
| 4 | Abbot |
| 2 | doris |
| 3 | Emerson |
| 4 | Green |
| 5 | eames |
| id | student |
| a | boris |
| 2 | Abbot |
13 | Green |
| 4 | €merson |
| | deames |
Note:
If the number of students is odd, there is no need to change the last one’s seat.
Solution
SELECT
IF (id¢(SELECT MAX(id) FROM seat) ,IF(id%2-0,id-1, id+1),IF(id¥2-0, id-2, id)) AS 4FROM seat
ORDER BY id;
627. Swap Salary | LeetCode
Table: salary
| Column Name | Type |
| ad | ant \
| name | varchar |
| sex | enum |
| salary | int |
id is the primary key for this table.
The sex column is ENUM value of type ('m', ‘f')
The table contains information about an employee.
Write an SQL query to swap all 'f" and ‘n’ values (.e., change all “#" values to
‘mn’ and vice versa) with a single update statement and no intermediate temp
table(s).
Note that you must write a single update statement, DO NOT write any select
statement for this problem.
The query result format is in the following example:
Salary table:
| id | name | sex | salary |
fa [a |[m | 25¢0 |12 18 [Ff | 150 |
13 1c |m | s5see |
l4 [0 |F | 500 |
| id | name | sex | salary |
la [a 1 | 25e |
12 18 [m | 15¢ |
13 lc lf [5s |
|4 [0 |m | se |
(2, A) and (2, C) were changed from ‘m' to 'f'.
(2, 8) and (4, D) were changed from ‘f' to ‘nm’.
Solution
UPDATE Salary SET sex
UPDATE Salary SET sex
CASE WHEN sex=
THEN '#" ELSE ‘m' END
1045. Customers Who Bought All Products
LeetCode
Medium | @
Table: customer
| customer_id | int || product_key | int |
product_key is a foreign key to Product table. Table: Product
| Column Name | Type |
| product_key | int |
product_key is the primary key column for this table.
Write an SQL query for a report that provides the customer ids from the Customer
table that bought all the products in the Product table.
For example:
Customer table:
| customer_id | product_key |
Ia
12
13
13
fa
Product table:
| product_key |Result table:
| custoner_id |
‘The customers who bought all the products (5 and 6) are customers with id 1 and 3
=a
Solution
SELECT customer_id
FROM Customer
GROUP NY customer_id
HAVING count (DISTINCT product_key) = (
SELECT count(1)
FROM Product)
1050. Actors and Directors Who Cooperated At Least Three
‘Times | Easy | (@) LeetCode
Table: ActorDirector
| Column Name | Type |
| actorid = | int |
| director_id | int
| timestamp | int |
timestamp is the primary key colunn for this table.Write a SQL query for a report that provides the pairs (actorid, directorid) where the
actor have cooperated with the director at least 3 times.
Example:
ActorDirector table:
| actor_id | director_id | timestamp |
pune
ja
fa
[a
fa
Ja
[2
12
Result table:
| actorid | director_id |
Ia
The only pair is (1, 1) where they cooperated exactly 3 times.
Solution
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(1)>=3
1068. Product Sales Analysis I | Easy
@ LeetCodeTable: sales
| saleid = | int |
| product_id | int |
| year [ant |
| quantity = | int |
| price [ant |
(sale_id, year) is the primary key of this table.
product_id is a foreign key to Product table.
Note that the price is per unit.
Table: Product
| column Name | Type |
| product_id | int |
| product_name | varchar |
product_id is the primary key of this table.
Write an SQL query that reports all product names of the products in the Sales table
along with their selling year and price.
For example:
Sales table
| sale_id | product_id | year | quantity | price |
la | 108 | 2008 | 10 | seee |2 | 100 | 2009 | 12 | seea |
7 | 208 | 2e12 | 15 | 9000 |
Product table:
| product_id | product_nane |
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
Result table:
| product_name | year | price |
| Nokia | 2008 | seee |
| Nokia | 2009 | 5000 |
| Apple | 2e11 | 9e0e |
Solution
SELECT product_name, year, price
FROM Sales JOIN Product
ON Product.product_id = Sales.product_id
1069. Product Sales Analysis II | Easy | @ LeetCode
Table: sales
| Column Name | Type || sale_id [int |
| product_id | int |
| year [int |
| quantity | int |
| price [int |
sale_id is the primary key of this table.
product_id is a foreign key to Product table.
Note that the price is per unit.
Table: product
| Column Name | Type |
| productid | int = |
| product_name | varchar |
product_id is the primary key of this table.
Write an SQL query that reports the total quantity sold for every product id.
The query result format is in the following example:
Sales table
| sale_id | product_id | year | quantity | price |
fa | 1¢@ | 2008 | 10 | seee |
[2 | 100 | 2009 | 12 | seea |
\7 | 206 | 2611 | a5 | 9eee |
Product table:| product_id | product_nane |
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
Result table:
| product_id | total_quantity |
Solution
SELECT product_id, sum(quantity) AS total_quantity
FROM Sales
GROUP BY product_id;
1070. Product Sales Analysis Ill | Medium | {i LeetCode
Table: sales
| saleid | int |
| product_id | int |
| year [ant |
| quantity | int |
| price [ant |sale_id is the primary key of this table.
product_id is a foreign key to Product table.
Note that the price is per unit.
Table: Product
| Colum Name | Type = |
| product_id | int
| product_name | varchar |
product_id is the primary key of this table.
Write an SQL query that selects the product id, year, quantity, and price for the first
year of every product sold.
The query result format is in the following example:
Sales table
| sale_id | product_id | year | quantity | price |
ha | 108 | 2608 | 10 | seee |
12 108 2009 | 12 5000
7 | 206 | 2611 | a5 | 9080 |
Product table:
| product_id | product_nane |
| 100 | Nokia |
Apple
| 300 | Samsung |Result table:
| product_id | first_year | quantity | price |
| 100 | 2008 | 10 | see |
| 200 | 2011 | a | 9000 |
Solution
a
SELECT
product_id,
year first_year,
quantity,
price
FROM Sales
WHERE (product_id, year) IN (SELECT product_id, MIN(year)
FROM Sales
GROUP BY product_id)
1075. Project Employees I | Easy | (@ LeetCode
Table: Project
| Column Name | Type |
| project_id | int |
| enployee_id | int |
(project_id, employee id) is the primary key of this table.
employee_id is a foreign key to Employee table.Table: Employee
| Column Name | type |
| employee_id [aint |
| name | varchar |
| experience years | int |
employee_id is the primary key of this table.
Write an SQL query that reports the average experience years of all the employees
for each project, rounded to 2 digits.
The query result format is in the following example:
Project table:
| project_id | employee_id |
Employee table:
| employee_id | name | experience_years |
| Khaled |
[au |
| ohn |
|
RUN
| DoeResult table:
| project_id | average_years |
‘The average experience years for the first project is (3 +2 +1) / 3 = 2.00 and
—
Solution
SELECT
p.project_id,
ROUND(AVG(e.experience_years),2) average_years
FROM
Project p JOIN Employee e ON
p-enployee_id = e.enployee_id
GRouP BY
p.project_id
1076. Project Employees II | Easy | (@ LeetCode
Table: Project
| Column Name | Type |
| project_id | int |
| employee_id | int |
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.Table: Employee
| Column Name | type |
| employee_id [aint |
| name | varchar |
| experience years | int |
employee_id is the primary key of this table.
Write an SQL query that reports all the projects that have the most employees.
The query result format is in the following example:
Project table:
| project_id | employee_id |
ha
ja
ja
2
12
Employee table:
| employee_id | name | experience_years |
| Khaled |
Jai |
| ohn |
|
| doe
Result table:| project_id |
‘The first project has 3 employees while the second one has 2.
SELECT project_id
FROM Project
GROUP BY project_id
HAVING COUNT(employee_id) = (SELECT COUNT(employee_id)
FROM Project
GROUP BY project_id
ORDER BY COUNT(employee_id) DESC
LIMIT 1)
1077. Project Employees II | Medium | ( LeetCode
Table: Project
| Column Nane | Type |
| project_id | int |
| employee_id | int |
(project_id, employee id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Table: Employee| Column Name | type |
| employee_id [aint |
| name | varchar |
| experience years | int |
employee_id is the primary key of this table.
Write an SQL query that reports the most experienced employees in each project. In
case of a tie, report all employees with the maximum number of experience years.
The query result format is in the following exampl
Project table:
| project_id | employee_id |
fa
la
fa
12
2
Employee table:
| employee_id | name | experience_years |
a | Khaled |
2 Jal |
3 | John |
4 |
| Doe| project_id | employee id |
la
fa 13 |
12
Both employees with id 1 and 3 have the most experience among the employees of th
a
SELECT
p-project_id,
e.enployee_id
FROM
Project p LEFT JOIN Employee e ON
p.enployee_id = e.enployee_id
WHERE (p.project_id,
e.experience_years) IN (SELECT
P.project_id,
MAX(e.experience_years)
FROM
Project p JOIN Employee e ON
p.enployee_id = e.employee_id
GROUP By
P.project_id)
1082. Sales Analysis I | Easy | (@ LeetCode
Table: Product
| Column Wane | Type| productid | int |
| product_name | varchar |
| unit_price | int |
product_id is the primary key of this table.
Table: Sales
| Column Name | Type = |
| seller_id | int I
| product_id | int |
| buyerid | int |
| saledate | date |
| quantity — | int |
| price [aint |
This table has no primary key, it can have repeated rows.
product_id is a foreign key to Product table.
Write an SQL query that reports the best seller by total sales price, If there is a tie,
report them all.
The query result format is in the following exampl
Product table:
| product_id | product_nane | unit_price |
ja | sa | 1000 |
2 G4 800 |
13 | Phone | 1400 |
Sales table| seller_id | product_id | buyer_id | sale_date | quantity | price |
\a ie \a | 2e19-e1-21 | 2 | 2000 |
ja 12 12 | 2019-02-17 | 2 | 800 |
j2 12 13 | 2019-06-02 | 1 | 800 |
13 13 \4 | 2019-05-13 | 2 | 2800 |
| sellerid |
la |
13 |
Both sellers with id 1 and 3 sold products with the most total price of 2800.
Solution
SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price) = (SELECT SUM(price)
FROM Sales
GROUP BY seller_id
ORDER BY SUM(price) DESC
LIMIT 1)
1083. Sales Analysis II | Easy | @ LeetCode
Table: Product| column Nae | Type |
| productid | int = |
| product_name | varchar |
| unit_price | int |
product_id is the primary key of this table.
Table: sales
| Column Name | Type = |
| sellerid | int |
| product_id | int |
| buyerid = | int |
| saledate | date |
| quantity — | int |
| price [aint |
This table has no primary key, it can have repeated rows.
product_id is a foreign key to Product table.
Write an SQL query that reports the buyers who have bought $8 but not iPhone.
Note that S8 and iPhone are products present in the Product table.
The query result format is in the following example:
Product table:
| product_id | product_nane | unit_price |
| s8 | 1000 I
| 800 |13 | 4Phone | 1400
Sales table
| seller_id | product_id | buyer_id | sale_date | quantity | price |
ja Ia |a | 2019-01-22 | 2 | 2000 |
\a 12 12 | 2019-02-17 | 1 | 800 |
j2 ie 13 | 2019-06-02 | 1 | 800 |
13 13 13 | 2019-05-13 | 2 | 2800 |
Result table:
| buyer_id = |
‘The buyer with id 1 bought an $8 but didn*t buy an iPhone. The buyer with id 3 bo
=
Solution
SELECT DISTINCT s.buyer_id
FROM Sales s LEFT JOIN Product p ON
s.product_id = p.product_id
WHERE p.product_name = 'S8" AND
s.buyer_id NOT IN (SELECT s.buyer_id
FROM Sales s LEFT JOIN Product p ON
s.product_id = p.product_id
WHERE p.product_nane = 'iPhone’)
1084. Sales Analysis III | Easy | (@ LeetCodeReports the products that were only sold in spring 2019. That is, between 2019-01-
01 and 2019-03-31 inclusive. Select the product that were only sold in spring 2019.
Product table:
| product_id | product_nane | unit_price |
[a | s8 | 1000 |
12 | 64 | 800 |
13 | 4Phone | 1400 |
| seller_id | product_id | buyer_id | saledate | quantity | price |
[a [a Ja | 2019-e1-21 | 2 | 200e |
\a 12 12 | 2019-02-17 | 4 | 800 |
j2 12 13 | 2019-06-02 | 4 | 800 |
13 13 \4 | 2019-05-13 | 2 | 2800 |
Result table:
| product_id | product_name |
The product with id 1 was only sold in spring 2019 while the other two were sold
=
Solution
(SELECT DISTINCT s.product_id, p.product_nane
FROM Sales s LEFT JOIN Product p ON
s-product_id = p.product_idWHERE s.sale_date >= '2019-01-01' AND
s.sale_date '2019-03-31')
1097. Game Play Analysis V | Hard | (@ LeetCode
We define the install date of a player to be the first login day of that player. We also
define day 1 retention of some date X to be the number of players whose install date
is X and they logged back in on the day right after X , divided by the number of
players whose install date is X, rounded to 2 decimal places. Write an SQL query that
reports for each install date, the number of players that installed the game on that,
day and the day 1 retention. The query result format is in the following example:
Activity table:
| player_id | device_id | event_date | games_played |
fa 12 | 2016-03-01 | 5 |
[a [2 | 2016-03-02 | 6 \
[2 13 | 2017-06-25 | 1 |
13 [a | 2016-03-01 | @ \
13 14 | 2016-07-03 | 5 |
| install_dt | installs | Dayi_retention |
| 2016-03-01 | 2 | 2.50 I
| 2017-06-25 | 1 | 0.20 |Player 1 and 3 installed the game on 2016-03-@1 but only player 1 logged back in
Player 2 installed the game on 2017 -06-25 but didn't log back in on 2017-06-26 s
—_YS
Solution
SELECT
install_dt,
COUNT(player_id) installs,
ROUND (COUNT (retention) /COUNT(player_id),2) Day1_retention
FROM
¢
SELECT a.player_id, a.install_dt, b.event_date retention
FROM
(SELECT player_id, MIN(event_date) install_dt
FROM Activity
GROUP BY player_id) a LEFT JOIN Activity b ON
a.player_id = b.player_id AND
a-install_dt + 1=b.event_date
) aS tmp
GROUP By
install_dt
—
1098. Unpopular Books | Medium | (@ LeetCode
Table: Books
| Column Name | Type
| book_id | int |
| name | varchar |
| available from | datebook_id is the primary key of this table.
Table: orders
| column Name | Type |
| order_id [int |
| book_id | int |
| quantity [int |
| dispatch_date | date |
order_id is the primary key of this table.
book_id is a foreign key to the Books table.
Write an SQL query that reports the books that have sold less than 10 copies in the
last year, excluding books that have been available for less than 1 month from today.
Assume today is 2019-06-23.
The query result format is in the following example:
Books table
| book_id | name | available_from |
ja | "kalila And Denna” | 2010-01-01 |
12 | "28 Letters" | 2012-05-12 |
13 | "The Hobbit" | 2819-06-10 |
l4 | "13 Reasons Why" | 2019-@6-@1 |
Is | "The Hunger Games" | 2008-09-21 |
orders table:
| order_id | book_id | quantity | dispatch_date || 2018-07-26 |
| 2ers-11-05 |
| 2e19-06-11 |
| 2019-06-05 |
| 2019-06-20 |
| 2009-02-02 |
| |
la
|2
13
la
Is
le
|7
wue awe
2010-04-13
| book_id | nane |
| “kalila And Denna" |
| "28 Letters™ |
| “The Hunger Games" |
Solution
SELECT
b.book_id, b.name
FROM
Books b LEFT JOIN (
SELECT book_id, SUM(quantity) nsold
FROM Orders
WHERE dispatch_date BETWEEN '2@18-@6-23' AND '2019-06-23'
GROUP BY book_id
yo
ON b.book_id = 0.book_id
WHERE
(o.nsold 3@1107. New Users Daily Count | Medium | (@ LeetCode
Table: Traffic
| column Name | Type |
| user_id [int |
| activity [enum |
| activity date | date |
There is no primary key for this table, it may have duplicate rows.
The activity colunn is an ENUM type of (‘login', ‘logout’, ‘jobs’, ‘groups’, "hom
=
Write an SQL query that reports for every date within at most 90 days from today,
the number of users that logged in for the first time on that date. Assume today is
2019-06-30.
The query result format is in the following exampl
text
Traffic table:
| user_id | activity | activity date |
fa | login | 2e19-@5-e1 |
Ia | honepage | 2019-@5-01 |
fa | logout | 2e19-@5-e1 |
12 | login | 2e19-@6-21 |
|2 | logout | 2e19-@6-21 |
13 | login | 2e19-e1-e1 |
13 | jobs | 2e19-e1-e1 |
13 | logout | 2019-e1-01 |
14 | login | 2e19-@6-21 |
ja | groups | 2019-06-21 |
l4 | logout | 2e19-e6-21 |
Is | login | 2019-03-21 |Is | logout | 2019-83-01 |
[5 | login | 2e19-@6-21 |
Is | logout | 2019-06-21 |
Result table:
| login_date | user_count
| 2019-@5-e1
| 2019-06-22
Note that we only care about dates with non zero user count.
‘The user with id 5 first logged in on 2019-03-01 so he's not counted on 2619-26-2
=
Solution
SELECT login_date, COUNT(user_id) AS user_count:
FROM (SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = ‘login’
GROUP BY user_id) AS t
WHERE login_date >= DATE_ADD('2019-06-30", INTERVAL -9@ DAY) AND login date «
GROUP BY login_date
SELECT login_date, COUNT(user_id) user_count
FROM,
(SELECT user_id, MIN(activity_date) as login_date
FROM Traffic
WHERE activity="login*
GROUP BY user_id) as t
WHERE DATEDIFF('2019-@6-30", login_date) event_avg
GROUP BY business_id
HAVING COUNT(event_type) > 1
1127. User Purchase Platform | Hard | (@ LeetCode
Table: spending| column Name | Type |
Juserid = | int |
| spend_date | date |
| platforn | enum |
| amount, | int |
The table logs the spendings history of users that make purchases from an online
(user_id, spend_date, platform) is the primary key of this table.
The platform column is an ENUM type of (‘desktop', ‘mobile').
—_-,
Write an SQL query to find the total number of users and the total amount spent
using mobile only, desktop only and both mobile and desktop together for each
date.
The query result format is in the following example:
Spending table:
| user_id | spend_date | platform | anount |
ja | 2019-07-01 | mobile | 100 |
fa | 2019-@7-@1 | desktop | 100
12 | 2019-07-01 | mobile | 100 |
[2 | 2019-@7-@2 | mobile | 106
13 | 2019-07-01 | desktop | 100 |
13 | 2019-27-02 | desktop | 100 |
| 2019-07-01 | desktop | 100
| 2e19-@7-@1 | mobile | 10¢| 2019-07-01 | both | 200 Ja \
| 2e19-@7-@2 | desktop | 100 fa |
| 2019-07-02 | mobile | 100 Ja \
| 2019-@7-@2 | both le le |
On 2019-07-01, user 1 purchased using both desktop and mobile, user 2 purchased u
On 2019-07-@2, user 2 purchased using mobile only, user 3 purchased using desktop
—_—_—_—,1
Solution
SELECT aa.spend_date,
2a.platforn,
COALESCE (bb.total_anount, @) total_anount,
COALESCE(bb.total_users,@) total_users
FROM
(SELECT DISTINCT(spend_date), a.platforn
FROM Spending JOIN
(SELECT ‘desktop’ AS platform UNION
SELECT ‘mobile’ AS platform UNION
SELECT "both’ AS platform
da
) 2a
LEFT 30IN
(SELECT spend_date,
platform,
SUM(anount) total_amount,
COUNT(user_id) total_users
FROM
(SELECT spend_date,
user_id,
(CASE COUNT (DISTINCT platform)
WHEN 1 THEN platform
WHEN 2 THEN ‘both
END) platform,
SUM(amount) anount
FROM SpendingGROUP BY spend_date, user_id
yb
GROUP BY spend_date, platform
) bb
ON aa.platform = bb.platform AND
aa.spend_date = bb.spend_date
1132. Reported Posts II | Medium | (@ LeetCode
Table: Actions
| column Name | Type |
| user_id | int |
| post_id Jaint |
| actiondate | date |
| action J enum |
| extra | varchar |
There is no primary key for this table, it may have duplicate rows.
The action column is an ENUM type of (‘view', ‘like’, ‘reaction’, ‘comment’, ‘rep
‘The extra column has optional information about the action such as a reason for r
—
Table: Removals
| Column Name | Type |
| post_id | int |
| remove date | date |
post_id is the primary key of this table.
Each row in this table indicates that some post was removed as a result of being—
Write an SQL query to find the average for daily percentage of posts that got
removed after being reported as spam, rounded to 2 decimal places.
The query result format is in the following example:
Actions table:
| user_id | post_id | action_date |
2019-07-01
2019-07-01
2019-07-01
2019-07-04
2019-07-04
|
|
|
|
Il
| 2019-07-04
| 2019-07-04
|
|
|
I
|
Il
2019-07-02
2019-07-02
1
1
1
2
2
3
3
4
4
5
2019-07-03
2019-07-03
2019-07-03
VUN NYU Re N Be
2019-07-03
action
view
Like
share
report
report
report
report
report
extra |
null |
null |
null |
null |
spam |
null |
spam |
null |
spam |
null |
racism |
null |
racism |
Removals table:
| post_id | remove_date |
12 | 2019-07-20 |
13 | 2019-07-18 |The percentage for 2019-07-04 is 5% because only one post of two spam reported p
The percentage for 2019-07-02 is 100% because one post was reported as spam and i
‘The other days had no spam reports so the average is (5@ + 100) / 2 = 75%
Note that the output is only one number and that we do not care about the remove
Solution
WITH t2 AS(
——TT—
v
SELECT a.action_date, (COUNT(DISTINCT r.post_id))/(COUNT (DISTINCT a.post_id)) AS
FROM (SELECT action date, post_id
FROM actions
WHERE extra = ‘spam’ AND action = ‘report') a
LEFT 301N
removals r
ON a.post_id = r.post_id
GROUP BY a.action_date)
SELECT ROUND(AVG(t1.result)*10@,2) AS average_daily_percent
FROM t1.
1141. User Activity for the Past 30 Days I | Easy | (@ LeetCode
Table: activity
| column Name | Type |
user_id | int
session id | int
activity date | date
activity_type | enumThere is no primary key for this table, it may have duplicate rows.
‘The activity_type column is an ENUM of type (‘open_session’, ‘end_session', ‘scro
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.
—
Write an SQL query to find the daily active user count for a period of 30 days ending
2019-07-27 inclusively. A user was active on some day if he/she made at least one
activity on that day.
The query result format is in the following example:
Activity table:
| user_id | session_id | activity date |
| 2019-07-20
| 2019-07-20
| 2019-07-20
| 2019-07-28
| 2019-07-21
| 2019-07-21
|
|
|
|
|
ee Ree
2019-07-21
2019-07-21
2019-07-21
2019-06-25
Ja
la
fa
|2
|2
2
[3
13
13
l4
l4
2019-96-25
| 2¢19-07-26 | 2 Il
| 2019-07-21 | 2 |
Note that we do not care about days with zero active users.
activity type |
open_session
scroll_down
end_session
open_session
send_message
end_session
open_session
send_message
end_session
open_session
end_sessionSolution
SELECT activity date AS day, COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE activity date > '2019-06-26' AND activity date 1
ORDER BY 1
1158. Market Analysis I | Medium | (@ LeetCodeTable: users
| column Nave | Type |
| user_id [ant |
| join_date ldate |
| favorite_brand | varchar |
user_id is the primary key of this table.
This table has the info of the users of an online shopping website where users ca
Table: orders
| Column Name | Type |
| order_id | int |
| orderdate | date |
| itemid | int |
| buyer_id [int |
| seller_id | int |
order_id is the primary key of this table.
item_id is a foreign key to the Items table.
buyer_id and seller_id are foreign keys to the Users table.
Table: rtens
| column Name | Type |
| itemid [int |
| itembrand — | varchar |
—item_id is the primary key of this table.
Write an SQL query to find for each user, the join date and the number of orders
they made as a buyer in 2019.
The query result format is in the following example:
Users table
| userid | join.date | favorite_brand |
a | 2018-01-01 | Lenovo I
2 | 2018-02-29 | Samsung |
3 | 2018-01-19 | LG I
4 | 2018-05-21 | HP |
| order_id | order_date | itemid | buyer_id | seller_id |
| 2019-08-01 | 4
| 2e18-e8-e2 | 2
| 2019-08-03 | 3
| 2e18-e8-e4 | 1
| 2018-08-04 | 1
| 2e19-e8-5 | 2
auhune
Rue
eR NUe DN
Items table
| itemid | item_brand |
| Sansung
| LenovoResult table:
| buyer_id | join_date | orders _in_2019 |
| 2018-01-01 |
| 2018-02-09 |
| 2018-01-19 |
| 2018-05-21 |
ia
[2
13
|4
Solution
SELECT user_id AS buyer_id, join_date, coalesce(a.orders_in_2019,0)
FROM users
LEFT 30IN
(
SELECT buyer_id, coalesce(count(*), @) AS orders_in_2019
FROM orders 0
JOIN users u
ON u.user_id = o.buyer_id
WHERE extract(‘year’ FROM order_date) = 2019
GROUP BY buyer_id) a
ON users.user_id = a.buyer_id
1159. Market Analysis II | Hard | (@ LeetCode
Table: Users
| column Name | Type |
| user_id | int || join_date | date |
| favorite_brand | varchar |
user_id is the primary key of this table.
This table has the info of the users of an online shopping website where users ca
——T
Table: orders
| column Wane | Type |
| order_id [int |
| order_date | date |
| itemid [int |
| buyer_id | int |
| sellerid = | int |
order_id is the primary key of this table.
item_id is a foreign key to the Items table.
buyer_id and seller_id are foreign keys to the Users table.
Table: Items
| Column Name | Type |
| itemid | int |
| itembrand — | varchar |
item_id is the primary key of this table.
Write an SQL query to find for each user, whether the brand of the second item (by
date) they sold is their favorite brand, If a user sold less than two items, report the
answer for that user as no.It is guaranteed that no seller sold more than one item on a day.
The query result format is in the following example:
Users table
| user_id | join_date | favorite_brand |
| 2019-01-01 | Lenovo
| 2019-02-09 | Samsung
| 2e19-e1-19 | LG
| 2019-05-21 | HP
orders table:
| order_id | order_date | item_id | buyer_id | seller_id |
| 2e19-08-01 | 4 |
| 2019-08-02 | 2 |
| 2019-08-03 | 3 |
| 2019-08-04 | 1 |
| 2e19-e8-04 | 1 |
| 2019-08-05 | 2 |
a RN UUW
| Sansung
| Lenovo
| us
| HP
Result table:
| seller_id | 2nd_item_fav_brand |l2
13
yes
yes
‘The answer for the user with id 1 is no because they sold nothing.
The answer for the users with id 2 and 3 is yes because the brands of their secon
‘The answer for the user with id 4 is no because the brand of their second sold it
—
Solution
SELECT user_id AS seller_i
IF(ISNULL(item_brand), “no”, “yes") AS 2nd_item_fav_brand
FROM Users LEFT JOIN
(SELECT seller_id, item_brand
FROM Orders INNER JOIN Items
ON Orders. itemid = Items.iten_id
WHERE (seller_id, order_date) IN
(SELECT seller_id, MIN(order_date) AS order_date
FROM Orders
WHERE (seller id, order_date) NOT IN
(SELECT seller_id, MIN(order_date) FROM Orders GROUP BY seller_id)
GROUP BY seller_id)
past
ON Users.user_id = t.seller_id and favorite_brand = item_brand
WITH €1 AS(
SELECT user_id,
CASE WHEN favorite_brand = item_brand THEN "yes"
ELSE “no”
END AS 2nd_iten_fav_brand
FROM users u LEFT JOIN, item_brand, RANK() OVER(PARTITION BY seller_id ORDE
(SELECT o.item_id, seller_i
FROM orders o join items i
USING (item_id)) a
ON u.user_id = a.seller_id
WHERE a.rk = 2)
SELECT u.user_id AS seller_id, COALESCE(2nd_iten_fav_brand,"no") AS 2nd_iten_fav_
FROM users u LEFT JOIN t1
USING(user_id)
1164, Product Price at a Given Date | Medium | LeetCode
Table: Products
| Column Name Type
| productid | int |
| newprice | int |
| change_date | date |
(product_id, change date) is the primary key of this table.
Each row of this table indicates that the price of some product was changed to a
——TT—3
Write an SQL query to find the prices of all products on 2019-08-16. Assume the
price of all products before any change is 10.
The query result format is in the following example:
Products table:
| product_id | new_price | change_date |
Ja | 20 | 2019-08-14 |l2 | se | 2019-08-14 |
fa | 38 | 2@19-e8-15 |
Ja | 35 | 2019-08-16 |
|2 | 6s | 2019-08-17 |
13 | 20 | 2019-08-18 |
Result table:
| product_id | price |
|se |
35 |
|. |
l2
fa |
13
Solution
WITH t1 AS (
SELECT a.product_id, new_price
FROM(
SELECT product_id, max(change_date) AS date
FROM products
WHERE change_date
