Questions and Answers

Select operation

1757

Recyclable and Low Fat Products

Write an SQL query to find the ids of products that are both low fat and recyclable. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Products table:

product_id	low_fats	recyclable
0	Y	N
1	Y	Y
2	N	Y
3	Y	Y
4	N	N

Output:

product_id
1
3

Explanation: Only products 1 and 3 are both low fat and recyclable.

select product_id 
from Products 
where low_fats='Y' and recyclable='Y';

584

Find Customer Referee

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Customer table:

id	name	referee_id
1	Will	null
2	Jane	null
3	Alex	2
4	Bill	null
5	Zack	1
6	Mark	2

Output:

name
Will
Jane
Bill
Zack

select name 
from Customer 
where referee_id is null or referee_id !=2;

595

Big Countries

A country is big if: it has an area of at least three million (i.e., 3000000 km2), or it has a population of at least twenty-five million (i.e., 25000000).

Write an SQL query to report the name, population, and area of the big countries. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

World table:

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000

Output:

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

select name, population, area 
from World 
where area>=3000000 or population>=25000000;

1148

Article Views

Write an SQL query to find all the authors that viewed at least one of their own articles. Return the result table sorted by id in ascending order. The query result format is in the following example.

Example 1:

Input:

Views table:

article_id	author_id	viewer_id	view_date
1	3	5	2019-08-01
1	3	6	2019-08-02
2	7	7	2019-08-01
2	7	6	2019-08-02
4	7	1	2019-07-22
3	4	4	2019-07-21
3	4	4	2019-07-21

Output:

id
4
7

select distinct viewer_id as id 
from Views 
where author_id=viewer_id 
order by viewer_id;

1683

Invalid Tweets

Write an SQL query to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Tweets table:

tweet_id	content
1	Vote for Biden
2	Let us make America great again!

Output:

tweet_id
2

Explanation: Tweet 1 has length = 14. It is a valid tweet. Tweet 2 has length = 32. It is an invalid tweet.

select tweet_id 
from Tweets 
where char_length(content)>15;

Basic Joins

1378

Replace Employee ID With The Unique Identifier

Write an SQL query to show the unique ID of each user, If a user does not have a unique ID replace just show null. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Employees table:

id	name
1	Alice
7	Bob
11	Meir
90	Winston
3	Jonathan

EmployeeUNI table:

id	unique_id
3	1
11	2
90	3

Output:

unique_id	name
null	Alice
null	Bob
2	Meir
3	Winston
1	Jonathan

Explanation: Alice and Bob do not have a unique ID, We will show null instead. The unique ID of Meir is 2. The unique ID of Winston is 3. The unique ID of Jonathan is 1.

select employees.name,employeeuni.unique_id 
from employees 
left join employeeuni on employees.id=employeeuni.id;

1068

Product Sales Analysis I

Write an SQL query that reports the product_name, year, and price for each sale_id in the Sales table. Return the resulting table in any order. The query result format is in the following example.

Example 1:

Input:

Sales table:

sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Product table:

product_id	product_name
100	Nokia
200	Apple
300	Samsung

Output:

product_name	year	price
Nokia	2008	5000
Nokia	2009	5000
Apple	2011	9000

Explanation: From sale_id = 1, we can conclude that Nokia was sold for 5000 in the year 2008. From sale_id = 2, we can conclude that Nokia was sold for 5000 in the year 2009. From sale_id = 7, we can conclude that Apple was sold for 9000 in the year 2011.

select product.product_name, sales.year, sales.price 
from sales, product 
where sales.product_id=product.product_id;

1581

Customer Who Visited but Did Not Make Any Transactions

Write a SQL query to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits. Return the result table sorted in any order. The query result format is in the following example.

Example 1:

Input:

Visits

visit_id	customer_id
1	23
2	9
4	30
5	54
6	96
7	54
8	54

Transactions

transaction_id	visit_id	amount
2	5	310
3	5	300
9	5	200
12	1	910
13	2	970

Output:

customer_id	count_no_trans
54	2
30	1
96	1

Explanation: Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12. Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13. Customer with id = 30 visited the mall once and did not make any transactions. Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions. Customer with id = 96 visited the mall once and did not make any transactions. As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also, user 54 visited the mall twice and did not make any transactions.

select visits.customer_id, count(visits.visit_id) as count_no_trans
from visits
left join transactions on visits.visit_id=transactions.visit_id
where transactions.transaction_id is null
group by visits.customer_id;

197

Rising Temperature

Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday). Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Weather table:

id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

Output:

id
2
4

Explanation: In 2015-01-02, the temperature was higher than the previous day (10 -> 25). In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

select A.id 
from weather A, weather B
where datediff(A.recordDate,B.recordDate) = 1
and A.temperature > B.temperature;

1661

Average Time Of Process Per Machine

There is a factory website that has several machines each running the same number of processes. Write an SQL query to find the average time each machine takes to complete a process. The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run. The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Activity table:

machine_id	process_id	activity_type	timestamp
0	0	start	0.712
0	0	end	1.520
0	1	start	3.140
0	1	end	4.120
1	0	start	0.550
1	0	end	1.550
1	1	start	0.430
1	1	end	1.420
2	0	start	4.100
2	0	end	4.512
2	1	start	2.500
2	1	end	5.000

Output:

machine_id	processing_time
0	0.894
1	0.995
2	1.456

Explanation: There are 3 machines running 2 processes each. Machine 0's average time is ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894 Machine 1's average time is ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995 Machine 2's average time is ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

select machine_id, 
round(sum(case when activity_type = 'start' then -1*timestamp else timestamp end)/count(distinct process_id),3) as processing_time
from activity
group by machine_id;

577

Employee Bonus

Write an SQL query to report the name and bonus amount of each employee with a bonus less than 1000. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Employee table:

empId	name	supervisor	salary
3	Brad	null	4000
1	John	3	1000
2	Dan	3	2000
4	Thomas	3	4000

Bonus table:

empId	bonus
2	500
4	2000

Output:

name	bonus
Brad	null
John	null
Dan	500

select employee.name, bonus.bonus
from employee
left join bonus on employee.empId=bonus.empId
where ifnull(bonus,0)<1000;

1280

Students and Examinations

Write an SQL query to find the number of times each student attended each exam. Return the result table ordered by student_id and subject_name. The query result format is in the following example.

Example 1:

Input:

Students table:

student_id	student_name
1	Alice
2	Bob
13	John
6	Alex

Subjects table:

subject_name
Math
Physics
Programming

Examinations table:

student_id	subject_name
1	Math
1	Physics
1	Programming
2	Programming
1	Physics
1	Math
13	Math
13	Programming
13	Physics
2	Math
1	Math

Output:

student_id	student_name	subject_name	attended_exams
1	Alice	Math	3
1	Alice	Physics	2
1	Alice	Programming	1
2	Bob	Math	1
2	Bob	Physics	0
2	Bob	Programming	1
6	Alex	Math	0
6	Alex	Physics	0
6	Alex	Programming	0
13	John	Math	1
13	John	Physics	1
13	John	Programming	1

Explanation: The result table should contain all students and all subjects. Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time. Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam. Alex did not attend any exams. John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.

select s.student_id, s.student_name,sub.subject_name, 
count(e.student_id) as attended_exams
from students s
cross join subjects sub
left join examinations e
using (student_id, subject_name)
group by s.student_id, sub.subject_name
order by student_id, subject_name;

570

Managers With At Least 5 Direct Reports

Write an SQL query to report the managers with at least five direct reports. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Employee table:

id	name	department	managerId
101	John	A	None
102	Dan	A	101
103	James	A	101
104	Amy	A	101
105	Anne	A	101
106	Ron	B	101

Output:

name
John

select distinct g.name as name
from employee e join employee g
on g.id=e.managerid
group by g.id
having count(*)>=5;

1934

Confirmation Rate

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write an SQL query to find the confirmation rate of each user. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Signups table:

user_id	time_stamp
3	2020-03-21 10:16:13
7	2020-01-04 13:57:59
2	2020-07-29 23:09:44
6	2020-12-09 10:39:37

Confirmations table:

user_id	time_stamp	action
3	2021-01-06 03:30:46	timeout
3	2021-07-14 14:00:00	timeout
7	2021-06-12 11:57:29	confirmed
7	2021-06-13 12:58:28	confirmed
7	2021-06-14 13:59:27	confirmed
2	2021-01-22 00:00:00	confirmed
2	2021-02-28 23:59:59	timeout

Output:

user_id	confirmation_rate
6	0.00
3	0.00
7	1.00
2	0.50

Explanation: User 6 did not request any confirmation messages. The confirmation rate is 0. User 3 made 2 requests and both timed out. The confirmation rate is 0. User 7 made 3 requests and all were confirmed. The confirmation rate is 1. User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.

select user_id,round(avg(if(action = 'confirmed', 1, 0)), 2) as confirmation_rate
FROM Signups left join Confirmations using(user_id)
group by 1
order by 1;

Basic Aggregate Functions

620

Not Boring Movies

Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring". Return the result table ordered by rating in descending order. The query result format is in the following example.

Example 1:

Input:

Cinema table:

id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	Fantacy	8.6
5	House card	Interesting	9.1

Output:

id	movie	description	rating
5	House card	Interesting	9.1
1	War	great 3D	8.9

Explanation: We have three movies with odd-numbered IDs: 1, 3, and 5. The movie with ID = 3 is boring so we do not include it in the answer.

select * from cinema
where id%2!=0 and description!="boring"
order by rating desc;

1251

Average Selling Price

Write an SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Prices table:

product_id	start_date	end_date	price
1	2019-02-17	2019-02-28	5
1	2019-03-01	2019-03-22	20
2	2019-02-01	2019-02-20	15
2	2019-02-21	2019-03-31	30

UnitsSold table:

product_id	purchase_date	units
1	2019-02-25	100
1	2019-03-01	15
2	2019-02-10	200
2	2019-03-22	30

Output:

product_id	average_price
1	6.96
2	16.96

Explanation: Average selling price = Total Price of Product / Number of products sold. Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96 Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

select prices.product_id, ROUND(SUM(price * units) / SUM(units), 2) as average_price
from Prices join UnitsSold 
on prices.product_id = unitssold.product_id
and purchase_date between start_date and end_date
group by product_id;

1075

Project Employees I

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Project table:

project_id	employee_id
1	1
1	2
1	3
2	1
2	4

Employee table:

employee_id	name	experience_years
1	Khaled	3
2	Ali	2
3	John	1
4	Doe	2

Output:

project_id	average_years
1	2.00
2	2.50

Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50

select project_id, round(avg(experience_years),2) as average_years
from project
left join employee on project.employee_id=employee.employee_id
group by project_id;

1633

Percentage of Users Attended a Contest

Write an SQL query to find the percentage of the users registered in each contest rounded to two decimals. Return the result table ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order. The query result format is in the following example.

Example 1:

Input:

Users table:

user_id	user_name
6	Alice
2	Bob
7	Alex

Register table:

contest_id	user_id
215	6
209	2
208	2
210	6
208	6
209	7
209	6
215	7
208	7
210	2
207	2
210	7

Output:

contest_id	percentage
208	100.0
209	100.0
210	100.0
215	66.67
207	33.33

Explanation: All the users registered in contests 208, 209, and 210. The percentage is 100% and we sort them in the answer table by contest_id in ascending order. Alice and Alex registered in contest 215 and the percentage is ((2/3) * 100) = 66.67% Bob registered in contest 207 and the percentage is ((1/3) * 100) = 33.33%

select contest_id, round(count(distinct user_id)/(select count(distinct user_id) from users)*100,2) as percentage
from register
group by contest_id
order by percentage desc, contest_id;

1211

Queries Quality and Percentage

We define query quality as: The average of the ratio between query rating and its position. We also define poor query percentage as: The percentage of all queries with rating less than 3. Write an SQL query to find each query_name, the quality and poor_query_percentage. Both quality and poor_query_percentage should be rounded to 2 decimal places. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Queries table:

query_name	result	position	rating
Dog	Golden Retriever	1	5
Dog	German Shepherd	2	5
Dog	Mule	200	1
Cat	Shirazi	5	2
Cat	Siamese	3	3
Cat	Sphynx	7	4

Output:

query_name	quality	poor_query_percentage
Dog	2.50	33.33
Cat	0.66	33.33

Explanation: Dog queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50 Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66 Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33

select query_name, 
round(avg(rating/position),2) as quality,
round(avg(rating<3)*100,2) as poor_query_percentage
from queries
group by query_name;

1193

Monthly Transactions I

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Transactions table:

id	country	state	amount	trans_date
121	US	approved	1000	2018-12-18
122	US	declined	2000	2018-12-19
123	US	approved	2000	2019-01-01
124	DE	approved	2000	2019-01-07

Output:

month	country	trans_count	approved_count	trans_total_amount	approved_total_amount
2018-12	US	2	1	3000	1000
2019-01	US	1	1	2000	2000
2019-01	DE	1	1	2000	2000

select left(trans_date,7) as month, country, count(id) as trans_count, sum(state='approved') as approved_count, sum(amount) as trans_total_amount,
sum(case
when state='approved' then amount
else 0
end) as approved_total_amount
from transactions
group by month,country;

1174

Immediate Food Delivery I

If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled. The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order. Write an SQL query to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places. The query result format is in the following example.

Example 1:

Input:

Delivery table:

delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2019-08-01	2019-08-02
2	2	2019-08-02	2019-08-02
3	1	2019-08-11	2019-08-12
4	3	2019-08-24	2019-08-24
5	3	2019-08-21	2019-08-22
6	2	2019-08-11	2019-08-13
7	4	2019-08-09	2019-08-09

Output:

immediate_percentage
50.00

Explanation: The customer id 1 has a first order with delivery id 1 and it is scheduled. The customer id 2 has a first order with delivery id 2 and it is immediate. The customer id 3 has a first order with delivery id 5 and it is scheduled. The customer id 4 has a first order with delivery id 7 and it is immediate. Hence, half the customers have immediate first orders.

select round(avg(order_date=customer_pref_delivery_date)*100,2) as immediate_percentage
from delivery where(customer_id, order_date)
in(select customer_id,min(order_date) as first_order
from delivery
group by customer_id);

550

Game Play Analysis IV

Write an SQL query to report 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.

Example 1:

Input:

Activity table:

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

Output:

fraction
0.33

Explanation: Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

select round(count(t2.player_id)/count(t1.player_id),2) as fraction
from 
(select player_id,min(event_date) as first_login 
from activity
group by player_id)
t1 left join activity t2
on t1.player_id=t2.player_id and t1.first_login=t2.event_date-1;

Sorting and Grouping

596

Classes More Than 5 Students

Write an SQL query to report all the classes that have at least five students. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Courses table:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

Output:

class
Math

Explanation:

• Math has 6 students, so we include it.
• English has 1 student, so we do not include it.
• Biology has 1 student, so we do not include it.
• Computer has 1 student, so we do not include it.

select class 
from Courses 
group by class 
having count(distinct student) >= 5;

1070

Product Sales Analysis III

Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold. Return the resulting table in any order. The query result format is in the following example.

Example 1:

Input:

Sales table:

sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Product table:

product_id	product_name
100	Nokia
200	Apple
300	Samsung

Output:

product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000

select product_id, year as first_year,quantity, price
from sales
where(product_id, year)
in
(select product_id, min(year) as year
from sales
group by product_id);

2356

Number Of Unique Subjects Taught By Each Teacher

Write an SQL query to report the number of unique subjects each teacher teaches in the university. Return the result table in any order. The query result format is shown in the following example.

Example 1:

Input:

Teacher table:

teacher_id	subject_id	dept_id
1	2	3
1	2	4
1	3	3
2	1	1
2	2	1
2	3	1
2	4	1

Output:

teacher_id	cnt
1	2
2	4

Explanation: Teacher 1:

• They teach subject 2 in departments 3 and 4.
• They teach subject 3 in department 3. Teacher 2:
• They teach subject 1 in department 1.
• They teach subject 2 in department 1.
• They teach subject 3 in department 1.
• They teach subject 4 in department 1.

select teacher_id, count(distinct(subject_id)) as cnt
from teacher
group by teacher_id;

1141

User Activity for the Past 30 Days I

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 someday if they made at least one activity on that day. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Activity table:

user_id	session_id	activity_date	activity_type
1	1	2019-07-20	open_session
1	1	2019-07-20	scroll_down
1	1	2019-07-20	end_session
2	4	2019-07-20	open_session
2	4	2019-07-21	send_message
2	4	2019-07-21	end_session
3	2	2019-07-21	open_session
3	2	2019-07-21	send_message
3	2	2019-07-21	end_session
4	3	2019-06-25	open_session
4	3	2019-06-25	end_session

Output:

day	active_users
2019-07-20	2
2019-07-21	2

Explanation: Note that we do not care about days with zero active users.

select distinct activity_date as day, count(distinct user_id) as active_users
from activity
where activity_date between '2019-06-28' and '2019-07-27'
group by activity_date;

1729

Find Followers Count

Write an SQL query that will, for each user, return the number of followers. Return the result table ordered by user_id in ascending order. The query result format is in the following example.

Example 1:

Input:

Followers table:

user_id	follower_id
0	1
1	0
2	0
2	1

Output:

user_id	followers_count
0	1
1	1
2	2

Explanation: The followers of 0 are {1} The followers of 1 are {0} The followers of 2 are {0,1}

select distinct user_id, count(follower_id) as followers_count
from followers
group by user_id
order by user_id;

619

Biggest Single Number

A single number is a number that appeared only once in the MyNumbers table. Write an SQL query to report the largest single number. If there is no single number, report null. The query result format is in the following example.

Example 1:

Input:

MyNumbers table:

num
8
8
3
3
1
4
5
6

Output:

num
6

Explanation: The single numbers are 1, 4, 5, and 6. Since 6 is the largest single number, we return it.

Example 2:

Input:

MyNumbers table:

num
8
8
7
7
3
3
3

Output:

num
null

Explanation: There are no single numbers in the input table so we return null.

select max(a.num) as num
from (select num from mynumbers group by num having count(*)=1) as a;

1045

Customers Who Bought All Products

Write an SQL query to report the customer ids from the Customer table that bought all the products in the Product table. Return the result table in any order. The query result format is in the following example.

Example 1:

Input:

Customer table:

customer_id	product_key
1	5
2	6
3	5
3	6
1	6

Product table:

product_key
5
6

Output:

customer_id
1
3

Explanation: The customers who bought all the products (5 and 6) are customers with IDs 1 and 3.

select customer_id
from customer
group by customer_id
having count(distinct product_key)=(select count(*) from product);