300 TOP LEEDCODE QUESTION, ANSWERING BY MYSQL

1 MYSQL - EASY

https://libaoj.in/LeetCode-Solutions/SQL/

https://towardsdatascience.com/sql-questions-summary-df90bfe4c9c

https://leetcode.ca/all/problems.html

https://circlecoder.com/leetcode-solution-list/

https://circlecoder.com/leetcode-database-solution-list/

2 Top travellers

Write an SQL query to report the distance travelled by each user.

Table: Users

 +---------------+---------+
 | Column Name   | Type    |
 +---------------+---------+
 | id            | int     |
 | name          | varchar |
 +---------------+---------+
 
 id is the primary key for this table.
 name is the name of the user.
 

Table: Rides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| user_id       | int     |
| distance      | int     |
+---------------+---------+
id is the primary key for this table.
user_id is the id of the user who travelled the distance "distance".
 

Write an SQL query to report the distance travelled by each user.

Return the result table ordered by travelled_distance in descending order, 
if two or more users travelled the same distance, order them by their name in 
ascending order.

The query result format is in the following example.

 

Users table:
+------+-----------+
| id   | name      |
+------+-----------+
| 1    | Alice     |
| 2    | Bob       |
| 3    | Alex      |
| 4    | Donald    |
| 7    | Lee       |
| 13   | Jonathan  |
| 19   | Elvis     |
+------+-----------+

Rides table:
+------+----------+----------+
| id   | user_id  | distance |
+------+----------+----------+
| 1    | 1        | 120      |
| 2    | 2        | 317      |
| 3    | 3        | 222      |
| 4    | 7        | 100      |
| 5    | 13       | 312      |
| 6    | 19       | 50       |
| 7    | 7        | 120      |
| 8    | 19       | 400      |
| 9    | 7        | 230      |
+------+----------+----------+

Result table:
+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
+----------+--------------------+
Elvis and Lee travelled 450 miles, Elvis is the top traveller as his name is 
alphabetically smaller than Lee. Bob, Jonathan, Alex and Alice have only one 
ride and we just order them by the total distances of the ride.
Donald didn't have any rides, the distance travelled by him is 0.

Note for Solution:

The COALESCE() function returns the first non-null value in a list.

Syntax:

COALESCE(val1, val2, ...., val_n)

2.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS USERS;
DROP TABLE IF EXISTS RIDES;
USE SQL_LEEDCODE;

DROP TABLE IF EXISTS USERS;
DROP TABLE IF EXISTS RIDES;


CREATE TABLE USERS (
    ID INT, 
    NAME VARCHAR(255),
    PRIMARY KEY (ID)
    
);
    



INSERT INTO USERS
VALUES (1 , "ALICE"),
(2 ,"BOB"),
(3 ,"ALEX"),
(4 ,"DONALD"),
(7 ,"LEE"),
(13 ,"JONATHAN"),
(19 ,"ELVIS");
    
    


    
    
SELECT 
    *
FROM
    USERS;




CREATE TABLE RIDES (
    ID INT, USER_ID INT , DISTANCE INT,
    PRIMARY KEY (ID)
    );



INSERT INTO RIDES
VALUES (1,1,120),
(2,2,317),
(3,3,222),
(4,7,100),
(5,13,312),
(6,19,50),
(7,7,120),
(8,19,400),
(9,7,230);
    
SELECT 
    *
FROM
    RIDES;

##################################################################
##                            ANSWER                            ##
##################################################################


SELECT
    U.NAME AS NAME, COALESCE(SUM(R.DISTANCE), 0) AS TRAVELLED_DISTANCE
FROM
    USERS U 
    LEFT JOIN RIDES R ON R.USER_ID = U.ID
GROUP BY
    NAME
ORDER BY
    TRAVELLED_DISTANCE DESC,NAME

3 Create Session bar chart

Table: Sessions
+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id is the primary key for this table.
duration is the time in seconds that a user has visited the
application.
You want to know how long a user visits your application. 
You decided to create 
bins of "[0-5>", "[5-10>", "[10-15>" and "15 minutes or more" 
and count the 
number of sessions on it.
Write an SQL query to report the (bin, total) in any order.
The query result format is in the following example.

Sessions table:

+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+

Result table:

+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+

For session_id 1, 2 and 3 have a duration greater or equal 
than 0 minutes and  less than 5 minutes.
For session_id 4 has a duration greater or equal than
5 minutes and less than 10 minutes.

There are no session with a duration greater or equial 
than 10 minutes and less than 15 minutes.
For session_id 5 has a duration greater or equal than 15 minutes.


##################################################################

The SQL UNION Operator: 

The UNION operator is used to combine the result-set of two or 
more SELECT statements.

- Every SELECT statement within UNION must have the same number 
of columns

- The columns must also have similar data types

- The columns in every SELECT statement must also be in the same order


UNION Syntax: 

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;


UNION ALL Syntax:

The UNION operator selects only distinct values by default. 
To allow duplicate  values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

#########################
##################################################################

The SQL CASE Expression: 
The CASE expression goes through conditions and returns a value 
when the first 
condition is met (like an if-then-else statement). So, 
once a condition is true,
it will stop reading and return the result. If no conditions are
true, it  returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

3.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS SESSIONS;

CREATE TABLE SESSIONS (
    SESSION_ID INT,
    DURATION INT,
    PRIMARY KEY (SESSION_ID)
);
 
INSERT INTO SESSIONS
VALUES (1 , 30),
(2 , 199),
(3 , 299),
(4 ,580) , (5 , 1000);


SELECT 
    *
FROM
    SESSIONS;
##################################################################
##                            ANSWER                            ##
##################################################################

SELECT 
    '[0-5>' AS BIN, COUNT(1) AS TOTAL
FROM
    SESSIONS
WHERE
    DURATION >= 0 AND DURATION < 300 
UNION SELECT 
    '[5-10>' AS BIN, COUNT(1) AS TOTAL
FROM
    SESSIONS
WHERE
    DURATION >= 300 AND DURATION < 600 
UNION SELECT 
    '[10-15>' AS BIN, COUNT(1) AS TOTAL
FROM
    SESSIONS
WHERE
    DURATION >= 600 AND DURATION < 900 
UNION SELECT 
    '15 OR MORE' AS BIN, COUNT(1) AS TOTAL
FROM
    SESSIONS
WHERE
    DURATION >= 900

4 Big Countries

There is a table World

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | int     |
+-------------+---------+
name is the primary key column for this table.

Each row of this table gives information about the name of a
country, the continent to which it belongs, its area, the 
population, and its GDP value.



+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

##################################################################

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.

 

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:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

4.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS WORLD;

CREATE TABLE IF NOT EXISTS WORLD (
    NAME VARCHAR(255),
    CONTINENT VARCHAR(255),
    AREA INT,
    POPULATION INT,
    GDP LONG
);
TRUNCATE TABLE WORLD;
INSERT INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) 
VALUES ('AFGHANISTAN', 'ASIA','652230', '25500100','20343000000');
INSERT INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES 
('ALBANIA', 'EUROPE', '28748', '2831741', '12960000000');
INSERT INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES
('ALGERIA', 'AFRICA', '2381741', '37100000', '188681000000');
INSERT INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES 
('ANDORRA', 'EUROPE', '468', '78115', '3712000000');
INSERT INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES 
('ANGOLA', 'AFRICA', '1246700', '20609294', '100990000000');

SELECT 
    *
FROM
    WORLD;


##################################################################
##                            ANSWER                            ##
##################################################################

SELECT 
    NAME, POPULATION, AREA
FROM
    WORLD
WHERE
    POPULATION > 25000000 OR AREA > 3000000;

5 Ads performance

Write an SQL query to find the ctr of each Ad.

Table: Ads

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+
(ad_id, user_id) is the primary key for this table.

Each row of this table contains the ID of an Ad, the ID of a 
user and the action
taken by this user regarding this Ad.
##################################################################

The action column is an ENUM type of ('Clicked', 'Viewed', 'Ignored').
 

A company is running Ads and wants to calculate the performance of
each Ad.

Performance of the Ad is measured using Click-Through Rate (CTR) 
where:


Write an SQL query to find the ctr of each Ad.

Round ctr to 2 decimal points. Order the result table by ctr in
descending order
and by ad_id in ascending order in case of a tie.

The query result format is in the following example:

Ads table:
+-------+---------+---------+
| ad_id | user_id | action  |
+-------+---------+---------+
| 1     | 1       | Clicked |
| 2     | 2       | Clicked |
| 3     | 3       | Viewed  |
| 5     | 5       | Ignored |
| 1     | 7       | Ignored |
| 2     | 7       | Viewed  |
| 3     | 5       | Clicked |
| 1     | 4       | Viewed  |
| 2     | 11      | Viewed  |
| 1     | 2       | Clicked |
+-------+---------+---------+


Result table:

+-------+-------+
| ad_id | ctr   |
+-------+-------+
| 1     | 66.67 |
| 3     | 50.00 |
| 2     | 33.33 |
| 5     | 0.00  |
+-------+-------+
##################################################################

for ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67
for ad_id = 2, ctr = (1/(1+2)) * 100 = 33.33
for ad_id = 3, ctr = (1/(1+1)) * 100 = 50.00
for ad_id = 5, ctr = 0.00, Note that ad_id = 5 has no clicks or views.
Note that we don't care about Ignored Ads.
Result table is ordered by the ctr. in case of a tie we order them
by ad_id

5.1 Solution

USE sql_leedcode;
DROP TABLE IF EXISTS ADS;
CREATE TABLE IF NOT EXISTS ADS (
    ad_id INT,
    user_id INT,
    action ENUM('Clicked', 'Viewed', 'Ignored'),
    PRIMARY KEY (ad_id , user_id)
);
TRUNCATE TABLE ADS;
INSERT INTO ADS VALUES ('1', '1', 'Clicked'),
('2', '2', 'Clicked'),
('3', '3', 'Viewed') ,
('2', '7', 'Viewed') ,
('5', '5', 'Ignored') ,
('7', '7', 'Ignored') ,
('3', '5', 'Clicked') , 
('1', '4', 'Viewed') ,
('2', '11', 'Viewed') ,
('1', '2', 'Clicked');

SELECT 
    *
FROM
    ADS;
    
##################################################################
##                            ANSWER                            ##
##################################################################  

/*
Return the specified value IF the expression is NULL, otherwise 
return the expression:
If the expression is NOT NULL, this function returns the expression.


Syntax:

IFNULL(expression, alt_value)


Round the number to 2 decimal places:

SELECT ROUND(135.375, 2);
result : 135.38
*/

SELECT 
    AD_ID,
    IFNULL(ROUND(SUM(ACTION = 'CLICKED') / SUM(ACTION != 'IGNORED') 
    * 100,2), 0) CTR
FROM
    ADS
GROUP BY AD_ID
ORDER BY CTR DESC , AD_ID;

6 Actors who cooperated with Directors atleast three times

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor have cooperated with the director at least 3 times.

Table: ActorDirector

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| actor_id    | int     |
| director_id | int     |
| timestamp   | int     |
+-------------+---------+
timestamp is the primary key column for this table.
 

Write a SQL query for a report that provides the pairs (actor_id,
director_id) 
where the actor have cooperated with the director at least 3 times.

Example:


Input: 


ActorDirector table:
+-------------+-------------+-------------+
| actor_id    | director_id | timestamp   |
+-------------+-------------+-------------+
| 1           | 1           | 0           |
| 1           | 1           | 1           |
| 1           | 1           | 2           |
| 1           | 2           | 3           |
| 1           | 2           | 4           |
| 2           | 1           | 5           |
| 2           | 1           | 6           |
+-------------+-------------+-------------+

Output: 


Result table:

+-------------+-------------+
| actor_id    | director_id |
+-------------+-------------+
| 1           | 1           |
+-------------+-------------+

Explanation: The only pair is (1, 1) where they cooperated exactly 3 times.


The only pair is (1, 1) where they cooperated exactly 3 times.

6.1 Solution

USE sql_leedcode;

DROP TABLE IF EXISTS ActorDirector;
CREATE TABLE IF NOT EXISTS ActorDirector (actor_id INT,
                        director_id INT,timestamp INT);
TRUNCATE TABLE ActorDirector;
INSERT INTO ActorDirector (actor_id, director_id, timestamp) 
VALUES ('1', '1', '0');
INSERT INTO ActorDirector (actor_id, director_id, timestamp) 
VALUES ('1', '1', '1');
INSERT INTO ActorDirector (actor_id, director_id, timestamp) 
VALUES ('1', '1', '2');
INSERT INTO ActorDirector (actor_id, director_id, timestamp) 
VALUES ('1', '2', '3');
INSERT INTO ActorDirector (actor_id, director_id, timestamp) 
VALUES ('1', '2', '4');
INSERT INTO ActorDirector (actor_id, director_id, timestamp) 
VALUES ('2', '1', '5');
INSERT INTO ActorDirector (actor_id, director_id, timestamp)
VALUES ('2', '1', '6');
SELECT * FROM ActorDirector;
##################################################################
##                            ANSWER                            ##
##################################################################
SELECT actor_id , director_id
FROM ActorDirector
GROUP BY actor_id = director_id 
#cooperated with the director at least 3 times
HAVING COUNT(*)>=3 ; # Condition for Group by

7 Article views

Table: Views
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
##################################################################

There is no primary key for this table, it may have duplicate rows.
Each row of this table indicates that some viewer viewed an article
(written by some author) on some date. 

Note that equal author_id and viewer_id indicate the same person.
Write an SQL query to find all the authors that viewed at least one 
of their own articles, sorted in ascending order by their id.

The query result format is in the following example:

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

Result table:

+------+
| id   |
+------+
| 4    |
| 7    |
+------+

7.1 Solution

USE sql_leedcode;
DROP TABLE IF EXISTS Views;
Create table If Not Exists Views (article_id int, author_id int,
viewer_id int, view_date date);
Truncate table Views;
insert into Views (article_id, author_id, viewer_id, view_date) 
values ('1', '3', '5', '2019-08-01');
insert into Views (article_id, author_id, viewer_id, view_date)
values ('1', '3', '6', '2019-08-02');
insert into Views (article_id, author_id, viewer_id, view_date) 
values ('2', '7', '7', '2019-08-01');
insert into Views (article_id, author_id, viewer_id, view_date) 
values ('2', '7', '6', '2019-08-02');
insert into Views (article_id, author_id, viewer_id, view_date)
values ('4', '7', '1', '2019-07-22');
insert into Views (article_id, author_id, viewer_id, view_date)
values ('3', '4', '4', '2019-07-21');
insert into Views (article_id, author_id, viewer_id, view_date)
values ('3', '4', '4', '2019-07-21');
SELECT * FROM Views;
##################################################################
##                            ANSWER                            ##
##################################################################
SELECT
    DISTINCT AUTHOR_ID AS ID
FROM
    VIEWS
WHERE
    AUTHOR_ID = VIEWER_ID
ORDER BY
    AUTHOR_ID

8 Biggest Single number

Can you write a SQL query to find the biggest number, which only appears once.

Table my_numbers contains many numbers in column num including 
duplicated ones.
Can you write a SQL query to find the biggest number, which only 
appears once.
+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 | 
For the sample data above, your query should return the following
result:

+---+
|num|
+---+
| 6 |
Note:
If there is no such number, just output null.

8.1 Solution

USE sql_leedcode;
Drop Table If Exists Number;
Create Table If Not Exists Number (Num Int);
Truncate Table Number;
Insert Into Number (Num) Values ('8');
Insert Into Number (Num) Values ('8');
Insert Into Number (Num) Values ('3');
Insert Into Number (Num) Values ('3');
Insert Into Number (Num) Values ('1');
Insert Into Number (Num) Values ('4');
Insert Into Number (Num) Values ('5');
Insert Into Number (Num) Values ('6');
Select * From Number;
##################################################################
##                            Answer                            ##
##################################################################
# Write Your Mysql Query Statement Below
Select Max(Num) As Num
From (Select Num From Number
      Group By Num
      Having Count(Num) = 1) As T;

9 Classes more than 5 students

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| student     | varchar |
| class       | varchar |
+-------------+---------+
(student, class) is the primary key column for this table.
Each row of this table indicates the name of a student and the
class in which they are enrolled.

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.

9.1 Solution

USE SQL_LEEDCODE;
CREATE TABLE IF NOT EXISTS COURSES (STUDENT VARCHAR(255),
                                    CLASS VARCHAR(255));
TRUNCATE TABLE COURSES;
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('A', 'MATH');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('B', 'ENGLISH');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('C', 'MATH');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('D', 'BIOLOGY');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('E', 'MATH');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('F', 'COMPUTER');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('G', 'MATH');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('H', 'MATH');
INSERT INTO COURSES (STUDENT, CLASS) VALUES ('I', 'MATH');
SELECT * FROM COURSES;
##################################################################
##                            ANSWER                            ##
##################################################################


SELECT 
    CLASS 
FROM 
    COURSES 
GROUP BY CLASS 
HAVING COUNT(DISTINCT STUDENT) >= 5

10 Combine two tables

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

able: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+

| personId    | int     |
| lastName  
T  | varchar |
| firstName   | varchar |
+-------------+---------+
personId is the primary key column for this table.
This table contains information about the ID of some persons 
and their first and last names.
 

| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

PersonId is the primary key column for this table.


Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+

| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
##################################################################

addressId is the primary key column for this table.
Each row of this table contains information about the city and
state of one person with ID = PersonId.
 

Write an SQL query to report the first name, last name, city, and 
state of each person in the Person table. If the address of a 
personId  is not present in the Address table, report null instead.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
Output: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+
Explanation: 
There is no address in the address table for the personId = 1 
so we return null in their city and state.
addressId = 1 contains information about the address of 
personId = 2.

| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

AddressId is the primary key column 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

10.1 Solution

USE SQL_LEEDCODE;
CREATE TABLE IF NOT EXISTS PERSON (PERSONID INT, FIRSTNAME 
VARCHAR(255), LASTNAME VARCHAR(255));
CREATE TABLE IF NOT EXISTS ADDRESS (ADDRESSID INT, PERSONID 
INT, CITY VARCHAR(255), STATE VARCHAR(255));
TRUNCATE TABLE PERSON;
INSERT INTO PERSON (PERSONID, LASTNAME, FIRSTNAME) VALUES
('1', 'WANG', 'ALLEN');
INSERT INTO PERSON (PERSONID, LASTNAME, FIRSTNAME) VALUES 
('2', 'ALICE', 'BOB');
TRUNCATE TABLE ADDRESS;
INSERT INTO ADDRESS (ADDRESSID, PERSONID, CITY, STATE) VALUES 
('1', '2', 'NEW YORK CITY', 'NEW YORK');
INSERT INTO ADDRESS (ADDRESSID, PERSONID, CITY, STATE) VALUES 
('2', '3', 'LEETCODE', 'CALIFORNIA');
SELECT * FROM ADDRESS;
SELECT * FROM PERSON;
##################################################################
##                            ANSWER                            ##
##################################################################

SELECT P.FIRSTNAME , P.LASTNAME , A.CITY , A.STATE
FROM PERSON P LEFT JOIN ADDRESS A ON P.PERSONID  = A.PERSONID

11 Consecutive available seats

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 seat_id using the following cinema table?

| seat_id | free |
|****************|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |

Your query should return the following result for the sample case above.

| seat_id |
|---------|
| 3       |
| 4       |
| 5       |

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.

11.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS CINEMA;
CREATE TABLE IF NOT EXISTS CINEMA (SEAT_ID INT PRIMARY KEY 
AUTO_INCREMENT, FREE BOOL);
TRUNCATE TABLE CINEMA;

INSERT INTO CINEMA (SEAT_ID, FREE) VALUES ('1', '1');
INSERT INTO CINEMA (SEAT_ID, FREE) VALUES ('2', '0');
INSERT INTO CINEMA (SEAT_ID, FREE) VALUES ('3', '1');
INSERT INTO CINEMA (SEAT_ID, FREE) VALUES ('4', '1');
INSERT INTO CINEMA (SEAT_ID, FREE) VALUES ('5', '1');
SELECT * FROM CINEMA;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT 
     *
FROM CINEMA A 
INNER JOIN CINEMA B 
ON ABS(A.SEAT_ID - B.SEAT_ID) = 1 
WHERE A.FREE = 1 AND B.FREE = 1 
ORDER BY A.SEAT_ID;

/*
Return the absolute value of a number:

SELECT Abs(-243.5) AS AbsNum; => 243.5
*/

SELECT 
     DISTINCT(A.SEAT_ID) 
FROM CINEMA A 
INNER JOIN CINEMA B 
ON ABS(A.SEAT_ID - B.SEAT_ID) = 1 
WHERE A.FREE = 1 AND B.FREE = 1 
ORDER BY A.SEAT_ID;

12 Customer placing the largest number of orders

Query the customer_number from the orders table for the customer who has placed the largest number of orders.

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
order_number is the primary key for this table.
This table contains information about the order ID 
and the customer ID.
 

Write an SQL query to find the customer_number for the
customer who has placed the largest number of orders.

The test cases are generated so that exactly one
customer will have placed more orders than any other customer.

The query result format is in the following example.

 

Example 1:

Input: 
Orders table:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
Output: 
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+
Explanation: 
The customer with number 3 has two orders, which is 
greater than either customer 1 or 2 because each of 
them only has one order. 
So the result is customer_number 3

12.1 Solution

Ranking them according to the number of orders to have same rank for customers with same number of orders

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS ORDERS;
CREATE TABLE IF NOT EXISTS ORDERS (ORDER_NUMBER INT, CUSTOMER_NUMBER INT);
TRUNCATE TABLE ORDERS;
INSERT INTO ORDERS (ORDER_NUMBER, CUSTOMER_NUMBER) VALUES ('1', '1');
INSERT INTO ORDERS (ORDER_NUMBER, CUSTOMER_NUMBER) VALUES ('2', '2');
INSERT INTO ORDERS (ORDER_NUMBER, CUSTOMER_NUMBER) VALUES ('3', '3');
INSERT INTO ORDERS (ORDER_NUMBER, CUSTOMER_NUMBER) VALUES ('4', '3');
SELECT * FROM ORDERS;
##################################################################
##                            ANSWER                            ##
##################################################################
# WRITE YOUR MYSQL QUERY STATEMENT BELOW

SELECT CUSTOMER_NUMBER
FROM ORDERS
GROUP BY CUSTOMER_NUMBER
ORDER BY COUNT(*) DESC
LIMIT 1;
##################################################################
##                            ANSWER2                           ##
##################################################################
SELECT
 CUSTOMER_NUMBER 
FROM
 (SELECT CUSTOMER_NUMBER, COUNT(ORDER_NUMBER) ORDER_COUNT 
  FROM ORDERS GROUP BY CUSTOMER_NUMBER) A 
ORDER BY ORDER_COUNT DESC LIMIT 1

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

Table: Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+

(product_id, start_date, end_date) is the primary 
key for this table.
Each row of this table indicates the price of the
product_id in the 
period from start_date to end_date.

For each product_id there will be no two overlapping
periods.
That means there will be no two intersecting periods 
for the same 
product_id.
 

Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
There is no primary key for this table, it may
contain duplicates.
Each row of this table indicates the date, units 
and product_id of each 
product sold. 
 

The query result format is in the following example:

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

Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
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

ToString("0.00") or similar to show decimals.

The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way 
that, columns with the same name of associated tables will appear once only.

Pictorial presentation of the above SQL Natural Join:

Natural Join: Guidelines

- The associated tables have one or more pairs of identically named columns.
- The columns must be the same data type.
- Don’t use ON clause in a natural join.

13.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS UNITSSOLD;
CREATE TABLE IF NOT EXISTS UNITSSOLD (PRODUCT_ID INT , 
                        PURCHASE_DATE DATE , UNITS INT);
TRUNCATE TABLE UNITSSOLD;
INSERT INTO UNITSSOLD  VALUES 
('1',"2019-02-25" ,'100'), ('1',"2019-03-01" ,'15'),
('2',"2019-02-10" ,'200'), ('2',"2019-03-22" ,'30');
SELECT * FROM UNITSSOLD;

DROP TABLE IF EXISTS PRICES;
CREATE TABLE IF NOT EXISTS PRICES (PRODUCT_ID INT , 
                        START_DATE DATE , END_DATE DATE , PRICE INT);
TRUNCATE TABLE PRICES;
INSERT INTO PRICES  VALUES 
('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');
SELECT * FROM PRICES;
##################################################################
##                            ANSWER                            ##
##################################################################
SELECT *
FROM UNITSSOLD U INNER JOIN PRICES P ON U.PRODUCT_ID = P.PRODUCT_ID
WHERE U.PURCHASE_DATE BETWEEN P.START_DATE AND P.END_DATE
GROUP BY U.PRODUCT_ID;

SELECT U.PRODUCT_ID, ROUND(SUM(UNITS*PRICE)/SUM(UNITS), 2) AS AVERAGE_PRICE
FROM UNITSSOLD U INNER JOIN PRICES P ON U.PRODUCT_ID = P.PRODUCT_ID
WHERE U.PURCHASE_DATE BETWEEN P.START_DATE AND P.END_DATE
GROUP BY U.PRODUCT_ID;

14 Customers who never order

Write a SQL query to find all customers who never order anything.

Suppose that a website contains two tables, 
the Customers table and the Orders table. 
Write a SQL query to find all customers who never order anything.
Table: Customers.
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Table: Orders.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Using the above tables as example, return the following:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+



The SQL ANY Operator

The ANY operator:

returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for
any of the values in the range.

ANY Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);




The SQL ALL Operator
The ALL operator:

returns a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is
true for all values in the range. 

ALL Syntax With SELECT


SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

14.1 Solution

USE sql_leedcode;

DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Orders;

CREATE TABLE Customers (
    Id int, 
    name varchar(255),
    PRIMARY KEY (id)
    
);
    
CREATE TABLE Orders (
    Id int, 
    CustomerId int);

INSERT INTO Customers
VALUES (1 , "Joe"),
(2 ,"Henry"),
(3 ,"Sam"),
(4 ,"Max");
    
INSERT INTO Orders
VALUES (1 , 3),
(2 ,1);

SELECT 
    *
FROM
    Customers;

##################################################################
##                            ANSWER                            ##
##################################################################



    
select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

15 Delete duplicate emails

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person table should
have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

15.1 Solution

USE sql_leedcode;

DROP TABLE IF EXISTS person;


CREATE TABLE person (
    id int, 
    email varchar(255),
    PRIMARY KEY (id)
    
);

INSERT INTO person
VALUES (1 , "john@example.com"),
(2 ,"bob@example.com"),
(3 ,"john@example.com");
    
    

SELECT * from person;


##################################################################
##                            ANSWER                            ##
##################################################################



DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;


  
SELECT * from person;

16 Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
For example, your query should return the following for the 
above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

16.1 Solution

USE sql_leedcode;
DROP TABLE IF EXISTS person;
CREATE TABLE person (
    id int, 
    email varchar(255),
    PRIMARY KEY (id)
    
);

INSERT INTO person
VALUES (1 , "a@b.com "),
(2 ,"c@d.com"),
(3 ,"a@b.com ");

SELECT * from person;

##################################################################
##                            ANSWER                            ##
##################################################################



SELECT email
 From   person
 Group  BY email
 Having Count(*) > 1

17 Employee Bonus

Select all employee’s name and bonus whose bonus is < 1000.

Table:Employee
+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId is the primary key column for this table.
Table: Bonus
+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId is the primary key column for this table.
Example ouput:
+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

17.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS BONUS;


CREATE TABLE EMPLOYEE (
    EMPID INT, 
    NAME VARCHAR(255),
    SUPERVISOR INT,
    SALARY INT,
    PRIMARY KEY (EMPID)
    
);


INSERT INTO EMPLOYEE
VALUES (1 , "JOHN" , 3 , 1000),
(2 , "DAN" , 3 , 2000), (3 , "BRAD" , NULL , 4000),
(4 , "THOMAS" , 3 , 4000);
    
    

SELECT * FROM EMPLOYEE;





 CREATE TABLE BONUS (
    EMPID INT, 
    BONUS INT,
    PRIMARY KEY (EMPID)
    
);  


INSERT INTO BONUS
VALUES (1 , 500),
(4  , 2000);

SELECT * FROM BONUS;

#SELECT ALL EMPLOYEES NAME AND BONUS WHOSE BONUS IS < 1000.#



INSERT INTO BONUS
VALUES (1 , 500),
(4  , 2000);
    
    

SELECT * FROM BONUS;

    
#SELECT ALL EMPLOYEES NAME AND BONUS WHOSE BONUS IS < 1000.#

##################################################################
##                            ANSWER                            ##
##################################################################

SELECT E.NAME , B.BONUS
FROM EMPLOYEE E LEFT JOIN BONUS B ON E.EMPID = B.EMPID
WHERE B.BONUS < 1000
    OR B.BONUS IS NULL;

18 Employees earning more than their managers

Given the Employee table, write a SQL query that finds out employees who earn more than their managers.

The Employee table holds all employees including their managers. 
Every employee has an Id, and there is also a column for the 
manager Id.
+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
For the above table, Joe is the only employee who earns more 
than his manager.
+----------+
| Employee |
+----------+
| Joe      |
+----------+

18.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS EMPLOYEE;


CREATE TABLE EMPLOYEE (
    ID INT, 
    NAME VARCHAR(255),
    SALARY INT, 
    MANAGERID INT
    
);


INSERT INTO EMPLOYEE
VALUES (1 , "JOE" , 70000 , 3 ),
(2 , "HENRY" , 80000 , 4), 
(3 , "SAM" , 60000 , NULL),
(4 , "MAX" , 90000 , NULL);
    
    

SELECT * FROM EMPLOYEE;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT
    A.NAME AS EMPLOYEE
FROM
    EMPLOYEE A,
    EMPLOYEE B
WHERE
    A.SALARY > B.SALARY
    AND A.MANAGERID = B.ID

19 Find Customer Refree

Given a table customer holding customers information and the referee. Write a query to return the list of customers NOT referred by the person with id ‘2’.

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+
Write a query to return the list of customers NOT referred
by the
person with id '2'.

For the sample data above, the result is:

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

19.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
    ID INT, 
    NAME VARCHAR(255),
    REFEREE_ID INT
    
);

INSERT INTO CUSTOMER
VALUES (1 , "WILL" , NULL ),
(2 , "JANE" , NULL), 
(3 , "ALEX" , 2),
(4 , "BILL" , NULL), (5 , "ZACK" , 1),
(6 , "MARK" , 2);
    
SELECT * FROM CUSTOMER;
##################################################################
##                            ANSWER                            ##
##################################################################



SELECT
    NAME
FROM
    CUSTOMER
WHERE
    REFEREE_ID != 2
    OR REFEREE_ID IS NULL

20 Find the team size

Write an SQL query to find the team size of each of the employees.

Table: Employee
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| team_id       | int     |
+---------------+---------+
employee_id is the primary key for this table.
Each row of this table contains the ID of each employee and their
respective team.
Write an SQL query to find the team size of each of the employees.
Return result table in any order.
The query result format is in the following example:
Employee Table:
+-------------+------------+
| employee_id | team_id    |
+-------------+------------+
|     1       |     8      |
|     2       |     8      |
|     3       |     8      |
|     4       |     7      |
|     5       |     9      |
|     6       |     9      |
+-------------+------------+
Result table:
+-------------+------------+
| employee_id | team_size  |
+-------------+------------+
|     1       |     3      |
|     2       |     3      |
|     3       |     3      |
|     4       |     1      |
|     5       |     2      |
|     6       |     2      |
+-------------+------------+
Employees with Id 1,2,3 are part of a team with team_id = 8.
Employees with Id 4 is part of a team with team_id = 7.
Employees with Id 5,6 are part of a team with team_id = 9.

20.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE IF NOT EXISTS EMPLOYEE (EMPLOYEE_ID INT, TEAM_ID INT , 
                                    PRIMARY KEY(EMPLOYEE_ID) );
TRUNCATE TABLE EMPLOYEE;
INSERT INTO EMPLOYEE VALUES 
('1', '8') ,('2', '8') ,('3', '8') , ('4', '7')  
,('5', '9') ,('6', '9');
SELECT * FROM EMPLOYEE;
##################################################################
##                            ANSWER                            ##
##################################################################
# WRITE YOUR MYSQL QUERY STATEMENT BELOW

SELECT A.EMPLOYEE_ID, COUNT(*) TEAM_SIZE
FROM EMPLOYEE A, EMPLOYEE B
WHERE A.TEAM_ID = B.TEAM_ID
GROUP BY A.EMPLOYEE_ID;

##################################################################
##                            ANSWER2                           ##
##################################################################
SELECT E.EMPLOYEE_ID, (SELECT COUNT(TEAM_ID) 
                        FROM EMPLOYEE 
                        WHERE E.TEAM_ID = TEAM_ID) AS TEAM_SIZE
FROM EMPLOYEE E

21 Friend Requests I: Overall Acceptance Rate

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|
|-----------|------------|------------|
| 1         | 2          | 2016_06-01 |
| 1         | 3          | 2016_06-01 |
| 1         | 4          | 2016_06-01 |
| 2         | 3          | 2016_06-02 |
| 3         | 4          | 2016-06-09 |
 

Table: request_accepted
| requester_id | accepter_id |accept_date |
|--------------|-------------|------------|
| 1            | 2           | 2016_06-03 |
| 1            | 3           | 2016-06-08 |
| 2            | 3           | 2016-06-08 |
| 3            | 4           | 2016-06-09 |
| 3            | 4           | 2016-06-10 |
 

Write a query to find the overall acceptance rate of 
requests rounded to 2 
decimals, which is the number of acceptance divide 
the number of requests.
 

For the sample data above, your query should return the 
following result.
 

|accept_rate|
|-----------|
|       0.80|
 

Note:
The accepted requests are not necessarily from the table 
friend_request. In this case, you just need to simply count 
the total accepted requests (no matter whether they are in 
the original requests), and divide it by the number of 
requests to get the acceptance rate.
It is possible that a sender sends multiple requests to 
the same receiver, and a request could be accepted more 
than once. In this case, the ‘duplicated’ requests or 
acceptances are only counted once.
If there is no requests at all, you should return 0.00 as
the accept_rate.
 

Explanation: There are 4 unique accepted requests, and there 
are 5 requests in total. 
So the rate is 0.80.

21.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS FRIEND_REQUEST;
DROP TABLE IF EXISTS REQUEST_ACCEPTED;

CREATE TABLE IF NOT EXISTS FRIEND_REQUEST (
 SENDER_ID INT NOT NULL,
 SEND_TO_ID INT NULL,
 REQUEST_DATE DATE NULL);
CREATE TABLE IF NOT EXISTS REQUEST_ACCEPTED (
 REQUESTER_ID INT NOT NULL,
 ACCEPTER_ID INT NULL,
 ACCEPT_DATE DATE NULL);

TRUNCATE TABLE FRIEND_REQUEST;
INSERT INTO FRIEND_REQUEST 
(SENDER_ID, SEND_TO_ID, REQUEST_DATE) 
 VALUES ('1', '2', '2016/06/01');
INSERT INTO FRIEND_REQUEST 
(SENDER_ID, SEND_TO_ID, REQUEST_DATE) 
 VALUES ('1', '3', '2016/06/01');
INSERT INTO FRIEND_REQUEST 
(SENDER_ID, SEND_TO_ID, REQUEST_DATE) 
 VALUES ('1', '4', '2016/06/01');
INSERT INTO FRIEND_REQUEST 
(SENDER_ID, SEND_TO_ID, REQUEST_DATE) 
 VALUES ('2', '3', '2016/06/02');
INSERT INTO FRIEND_REQUEST 
(SENDER_ID, SEND_TO_ID, REQUEST_DATE) 
 VALUES ('3', '4', '2016/06/09');

TRUNCATE TABLE REQUEST_ACCEPTED;
INSERT INTO REQUEST_ACCEPTED 
(REQUESTER_ID, ACCEPTER_ID, ACCEPT_DATE) 
 VALUES ('1', '2', '2016/06/03');
INSERT INTO REQUEST_ACCEPTED 
(REQUESTER_ID, ACCEPTER_ID, ACCEPT_DATE) 
 VALUES ('1', '3', '2016/06/08');
INSERT INTO REQUEST_ACCEPTED 
(REQUESTER_ID, ACCEPTER_ID, ACCEPT_DATE) 
 VALUES ('2', '3', '2016/06/08');
INSERT INTO REQUEST_ACCEPTED 
(REQUESTER_ID, ACCEPTER_ID, ACCEPT_DATE) 
 VALUES ('3', '4', '2016/06/09');
INSERT INTO REQUEST_ACCEPTED 
(REQUESTER_ID, ACCEPTER_ID, ACCEPT_DATE) 
 VALUES ('3', '4', '2016/06/10');


SELECT * FROM FRIEND_REQUEST;
SELECT * FROM REQUEST_ACCEPTED;

##################################################################
##                            ANSWER1                           ##
##################################################################
# WRITE YOUR MYSQL QUERY STATEMENT BELOW

SELECT IFNULL(ROUND(COUNT(DISTINCT REQUESTER_ID, ACCEPTER_ID) /
                    COUNT(DISTINCT SENDER_ID, SEND_TO_ID), 2), 0) 
AS ACCEPT_RATE
FROM REQUEST_ACCEPTED, FRIEND_REQUEST;

##################################################################
##                            ANSWER2                           ##
##################################################################

WITH T1 AS
(
    SELECT DISTINCT SENDER_ID, SEND_TO_ID
    FROM FRIEND_REQUEST
), T2 AS
(
    SELECT DISTINCT REQUESTER_ID, ACCEPTER_ID
    FROM REQUEST_ACCEPTED
)

SELECT 
IFNULL((SELECT DISTINCT ROUND((SELECT COUNT(*) FROM T2) / 
      ( SELECT COUNT(*) FROM T1),2) FROM T1,T2 ),0) 'ACCEPT_RATE';

22 Friendly Movies Streamed Last Month Problem

Write an SQL query to report the distinct titles of the kid-friendly movies streamed in June 2020.

Return the result table in any order.

The query result format is in the following example.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| program_date  | date    |
| content_id    | int     |
| channel       | varchar |
+---------------+---------+
(program_date, content_id) is the primary key for this table.
This table contains information of the programs on the TV.
content_id is the id of the program in some channel on the TV.


+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| content_id       | varchar |
| title            | varchar |
| Kids_content     | enum    |
| content_type     | varchar |
+------------------+---------+
content_id is the primary key for this table.
Kids_content is an enum that takes one of the values ('Y', 'N') where: 
'Y' means is content for kids otherwise 'N' is not content for kids.
content_type is the category of the content as movies, series, etc.


TVProgram table:
+--------------------+--------------+-------------+
| program_date       | content_id   | channel     |
+--------------------+--------------+-------------+
| 2020-06-10 08:00   | 1            | LC-Channel  |
| 2020-05-11 12:00   | 2            | LC-Channel  |
| 2020-05-12 12:00   | 3            | LC-Channel  |
| 2020-05-13 14:00   | 4            | Disney Ch   |
| 2020-06-18 14:00   | 4            | Disney Ch   |
| 2020-07-15 16:00   | 5            | Disney Ch   |
+--------------------+--------------+-------------+

Content table:
+------------+----------------+---------------+---------------+
| content_id | title          | Kids_content  | content_type  |
+------------+----------------+---------------+---------------+
| 1          | Leetcode Movie | N             | Movies        |
| 2          | Alg. for Kids  | Y             | Series        |
| 3          | Database Sols  | N             | Series        |
| 4          | Aladdin        | Y             | Movies        |
| 5          | Cinderella     | Y             | Movies        |
+------------+----------------+---------------+---------------+

Result table:
+--------------+
| title        |
+--------------+
| Aladdin      |
+--------------+
"Leetcode Movie" is not a content for kids.
"Alg. for Kids" is not a movie.
"Database Sols" is not a movie
"Alladin" is a movie, content for kids and was streamed in June 2020.
"Cinderella" was not streamed in June 2020.

22.1 Solution

USE SQL_LEEDCODE;
CREATE TABLE IF NOT EXISTS TVPROGRAM (PROGRAM_DATE DATETIME, CONTENT_ID 
INT, CHANNEL VARCHAR(255) , PRIMARY KEY(PROGRAM_DATE, CONTENT_ID));
CREATE TABLE IF NOT EXISTS CONTENT (CONTENT_ID VARCHAR(255), TITLE 
VARCHAR(255),KIDS_CONTENT ENUM('Y', 'N'), 
CONTENT_TYPE VARCHAR(255) ,PRIMARY KEY(CONTENT_ID));

TRUNCATE TABLE TVPROGRAM ;
TRUNCATE TABLE CONTENT ;

INSERT INTO TVPROGRAM VALUES
("2020-06-10 08:00" , "1" , "LC-Channel"), 
("2020-05-11 12:00" , "2" , "LC-Channel"), 
("2020-05-12 12:00" , "3" , "LC-Channel"), 
("2020-05-13 14:00" , "4" , "Disney Ch"), 
("2020-06-18 14:00" , "4" , "Disney Ch"), 
("2020-07-15 16:00" , "5" , "Disney Ch");


INSERT INTO CONTENT VALUES
('1', 'Leetcode Movie' ,'N', 'Movies'),
('2', 'Alg. for Kids' ,'Y', 'Series'),
('3', 'Database Sols' ,'N', 'Series'),
('4', 'Aladdin' ,'Y', 'Movies'),
('5','Cinderella' ,'Y', 'Movies');

SELECT * FROM TVPROGRAM;
SELECT * FROM CONTENT;
##################################################################
##                            ANSWER                            ##
##################################################################

SELECT DISTINCT TITLE
FROM CONTENT 
JOIN TVPROGRAM USING(CONTENT_ID)
WHERE KIDS_CONTENT = 'Y' 
    AND CONTENT_TYPE = 'MOVIES' 
    AND (MONTH(PROGRAM_DATE), YEAR(PROGRAM_DATE)) = (6, 2020)

23 Game Play Analysis 1

Write an SQL query to report the first login date for each player.

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | 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 games.
Each row is a record of a player who logged in and played a number 
of games (possibly 0) before logging out on someday using some 
device.
 

Write an SQL query to report the first login date for each player.

Return the result table in any order.

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-05-02 | 6            | 
| 2         | 3         | 2017-06-25 | 1            | *
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            | *
+-----------+-----------+------------+--------------+
Output: 
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

23.1 Solution

USE SQL_LEEDCODE;
Create table If Not Exists Activity (player_id int, device_id int,
 event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played)
 values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played)
 values ('1', '2', '2016-05-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) 
values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played)
 values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) 
values ('3', '4', '2018-07-03', '5');

SELECT * FROM Activity;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT PLAYER_ID, MIN(EVENT_DATE) AS FIRST_LOGIN
FROM ACTIVITY
GROUP BY PLAYER_ID;

24 Game play analysis 2

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | 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 (possibly 0) before logging out on some day using
some device.
 

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 |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

24.1 Solution

USE SQL_LEEDCODE;
Create table If Not Exists Activity (player_id int, device_id int,
 event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played)
 values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played)
 values ('1', '2', '2016-05-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) 
values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played)
 values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) 
values ('3', '4', '2018-07-03', '5');

SELECT * FROM Activity;

##################################################################
##                            ANSWER                            ##
##################################################################
WITH TABLE1 AS
(
   SELECT PLAYER_ID, DEVICE_ID,
   RANK() OVER(PARTITION BY PLAYER_ID
               ORDER BY EVENT_DATE) AS RK
   FROM ACTIVITY
)
SELECT T.PLAYER_ID, T.DEVICE_ID
FROM TABLE1 AS T
WHERE T.RK=1;

##################################################################
##                            ANSWER2                           ##
##################################################################
# WRITE YOUR MYSQL QUERY STATEMENT BELOW
SELECT PLAYER_ID, MIN(EVENT_DATE)
        FROM ACTIVITY
        GROUP BY PLAYER_ID;


SELECT PLAYER_ID, DEVICE_ID
    FROM ACTIVITY
    WHERE (PLAYER_ID, EVENT_DATE)
    IN (SELECT PLAYER_ID, MIN(EVENT_DATE)
        FROM ACTIVITY
        GROUP BY PLAYER_ID);

25 Group sold products by the date

Write an SQL query to find for each date, the number of distinct products sold and their names.

Table Activities:

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
There is no primary key for this table, it may contains 
duplicates.
Each row of this table contains the product name and the
date it was sold in a market.
 

Write an SQL query to find for each date, the number of
distinct products sold and their names.

The sold-products names for each date should be sorted 
lexicographically. 

Return the result table ordered by sell_date.

The query result format is in the following example.

Activities table:
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+

Result table:
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt),
we sort them lexicographically and separate them by comma.

For 2020-06-01, Sold items were (Pencil, Bible), we sort them 
lexicographically and separate them by comma.

For 2020-06-02, Sold item is (Mask), we just return it.

25.1 Solution

USE SQL_LEEDCODE;
CREATE TABLE IF NOT EXISTS ACTIVITIES (SELL_DATE DATE,
PRODUCT VARCHAR(20));
TRUNCATE TABLE ACTIVITIES;
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-05-30', 'HEADPHONE');
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-06-01', 'PENCIL');
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-06-02', 'MASK');
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-05-30', 'BASKETBALL');
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-06-01', 'BIBLE');
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-06-02', 'MASK');
INSERT INTO ACTIVITIES (SELL_DATE, PRODUCT) 
VALUES ('2020-05-30', 'T-SHIRT');

SELECT * FROM ACTIVITIES;

##################################################################
##                            ANSWER                            ##
##################################################################
# MYSQL GROUP_CONCAT() FUNCTION RETURNS A STRING WITH 
# CONCATENATED NON-NULL VALUE FROM A GROUP.
# RETURNS NULL WHEN THERE ARE NO NON-NULL VALUES.

SELECT SELL_DATE , COUNT(DISTINCT PRODUCT) AS NUM_SOLD , 
GROUP_CONCAT(DISTINCT PRODUCT) AS PRODUCTS
FROM ACTIVITIES 
GROUP BY SELL_DATE
ORDER BY SELL_DATE;

26 Immediate food delivery

Write an SQL query to find the percentage of immediate orders in the table, rounded to 2 decimal places.

Table: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id is the primary key of this table.
The table holds information about food delivery to customers 
that make orders at some date and specify a preferred delivery
date (on the same order date or after it).
 

If the preferred delivery date of the customer is the same 
as the order date then the order is called immediate otherwise 
it's called scheduled.


The query result format is in the following example:

Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 5           | 2019-08-02 | 2019-08-02                  |*
| 3           | 1           | 2019-08-11 | 2019-08-11                  |*
| 4           | 3           | 2019-08-24 | 2019-08-26                  |
| 5           | 4           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
+-------------+-------------+------------+-----------------------------+

Result table:
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+
The orders with delivery id 2 and 3 are immediate while the others 
are scheduled.

26.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS DELIVERY;

CREATE TABLE IF NOT EXISTS DELIVERY 
(DELIVERY_ID INT, CUSTOMER_ID INT,
ORDER_DATE DATE , CUSTOMER_PREF_DELIVERY_DATE DATE );

TRUNCATE TABLE DELIVERY;
INSERT INTO DELIVERY 
VALUES ("1","1","2019-08-01","2019-08-02"),
("2","5","2019-08-02","2019-08-02"),
("3","1","2019-08-11","2019-08-11"),
("4","3","2019-08-24","2019-08-26"),
("5","4","2019-08-21","2019-08-22"),
("6","2","2019-08-11","2019-08-13");

SELECT * FROM DELIVERY;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT 
ROUND(AVG(CASE 
            WHEN 
            ORDER_DATE=CUSTOMER_PREF_DELIVERY_DATE THEN 1 
            ELSE 0 END)* 100, 2) 
            AS IMMEDIATE_PERCENTAGE
FROM DELIVERY;
##################################################################
##                            ANSWER2                           ##
##################################################################

SELECT COUNT(*) *100/B.TOTAL AS IMMEDIATE_PERCENTAGE FROM DELIVERY A, 
(SELECT COUNT(*) AS TOTAL FROM DELIVERY) B
WHERE A.ORDER_DATE = A.CUSTOMER_PREF_DELIVERY_DATE;

27 List the products ordered in a period

Write an SQL query to get the names of products with greater than or equal to 100 units ordered in February 2020 and their amount.

Table: Products

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
+------------------+---------+
product_id is the primary key for this table.
This table contains data about the company's products.
Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
+---------------+---------+
There is no primary key for this table. It may have duplicate rows.
product_id is a foreign key to Products table.
unit is the number of products ordered in order_date.
 


Return result table in any order.

The query result format is in the following example:

 

Products table:
+-------------+-----------------------+------------------+
| product_id  | product_name          | product_category |
+-------------+-----------------------+------------------+
| 1           | Leetcode Solutions    | Book             |
| 2           | Jewels of Stringology | Book             |
| 3           | HP                    | Laptop           |
| 4           | Lenovo                | Laptop           |
| 5           | Leetcode Kit          | T-shirt          |
+-------------+-----------------------+------------------+

Orders table:
+--------------+--------------+----------+
| product_id   | order_date   | unit     |
+--------------+--------------+----------+
| 1            | 2020-02-05   | 60       |
| 1            | 2020-02-10   | 70       |
| 2            | 2020-01-18   | 30       |
| 2            | 2020-02-11   | 80       |
| 3            | 2020-02-17   | 2        |
| 3            | 2020-02-24   | 3        |
| 4            | 2020-03-01   | 20       |
| 4            | 2020-03-04   | 30       |
| 4            | 2020-03-04   | 60       |
| 5            | 2020-02-25   | 50       |
| 5            | 2020-02-27   | 50       |
| 5            | 2020-03-01   | 50       |
+--------------+--------------+----------+

Result table:
+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+

Products with product_id = 1 is ordered in February a total of (60 + 70) = 130.
Products with product_id = 2 is ordered in February a total of 80.
Products with product_id = 3 is ordered in February a total of (2 + 3) = 5.
Products with product_id = 4 was not ordered in February 2020.
Products with product_id = 5 is ordered in February a total of (50 + 50) = 100.

27.1 Solution

USE SQL_LEEDCODE;
DROP TABLE IF EXISTS PRODUCTS;

CREATE TABLE IF NOT EXISTS PRODUCTS 
(PRODUCT_ID INT,
PRODUCT_NAME VARCHAR(255) ,
 PRODUCT_CATEGORY VARCHAR(255), 
 PRIMARY KEY(PRODUCT_ID)
 );


TRUNCATE TABLE PRODUCTS;
INSERT INTO PRODUCTS 
VALUES ("1", "LEETCODE SOLUTIONS","BOOK"),
("2","JEWELS OF STRINGOLOGY","BOOK"),
("3","HP","LAPTOP"),
("4","LENOVO","LAPTOP"),
("5","LEETCODE KIT","T-SHIRT");

SELECT * FROM PRODUCTS;



DROP TABLE IF EXISTS ORDERS;

CREATE TABLE IF NOT EXISTS ORDERS 
(PRODUCT_ID INT,
ORDER_DATE DATE ,
 UNIT INT, 
 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID));


TRUNCATE TABLE ORDERS;
INSERT INTO ORDERS 
VALUES 
("1","2020-02-05","60"),
("1","2020-02-10","70"),
("2","2020-01-18","30"),
("2","2020-02-11","80"),
("3","2020-02-17","2 "),
("3","2020-02-24","3 "),
("4","2020-03-01","20"),
("4","2020-03-04","30"),
("4","2020-03-04","60"),
("5","2020-02-25","50"),
("5","2020-02-27","50"),
("5","2020-03-01","50");

SELECT * FROM ORDERS;
##################################################################
##                            ANSWER                            ##
##################################################################
SELECT  P.PRODUCT_NAME , SUM(O.UNIT) AS UNIT
FROM 
PRODUCTS P  JOIN ORDERS O ON P.PRODUCT_ID=O.PRODUCT_ID 
# WHERE O.ORDER_DATE BETWEEN "2020-02-01" AND "2020-02-28"
WHERE MONTH(O.ORDER_DATE)=2 AND YEAR(O.ORDER_DATE) = 2020 
GROUP BY O.PRODUCT_ID 
HAVING UNIT >=100;
##################################################################
##                            ANSWER                            ##
##################################################################

SELECT PRODUCT_NAME, SUM(UNIT) AS UNIT FROM PRODUCTS AS P 
JOIN ORDERS AS O 
ON P.PRODUCT_ID = O.PRODUCT_ID 
WHERE ORDER_DATE BETWEEN '2020-02-01' AND '2020-02-29' 
GROUP BY PRODUCT_NAME 
HAVING SUM(UNIT) >= '100'

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT A.PRODUCT_NAME, A.UNIT
FROM
(SELECT P.PRODUCT_NAME, SUM(UNIT) AS UNIT
FROM ORDERS O 
JOIN PRODUCTS P
ON O.PRODUCT_ID = P.PRODUCT_ID
WHERE MONTH(ORDER_DATE)=2 AND YEAR(ORDER_DATE) = 2020
GROUP BY O.PRODUCT_ID) A
WHERE A.UNIT>=100;

28 Not Boring movies

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions. Please write a SQL query to output movies with an odd numbered ID and a description that is not ‘boring’. Order the result by rating.

For example, table cinema:

+---------+-----------+--------------+-----------+
|   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     |
+---------+-----------+--------------+-----------+
For the example above, the output should be:
+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

28.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS CINEMA;

CREATE TABLE IF NOT EXISTS CINEMA (ID INT, MOVIE VARCHAR(255),
 DESCRIPTION VARCHAR(255), RATING FLOAT(2, 1));

TRUNCATE TABLE CINEMA;

INSERT INTO CINEMA (ID, MOVIE, DESCRIPTION, RATING)
 VALUES ('1', 'WAR', 'GREAT 3D', '8.9');
INSERT INTO CINEMA (ID, MOVIE, DESCRIPTION, RATING)
 VALUES ('2', 'SCIENCE', 'FICTION', '8.5');
INSERT INTO CINEMA (ID, MOVIE, DESCRIPTION, RATING)
 VALUES ('3', 'IRISH', 'BORING', '6.2');
INSERT INTO CINEMA (ID, MOVIE, DESCRIPTION, RATING)
 VALUES ('4', 'ICE SONG', 'FANTACY', '8.6');
INSERT INTO CINEMA (ID, MOVIE, DESCRIPTION, RATING)
 VALUES ('5', 'HOUSE CARD', 'INTERESTING', '9.1');

SELECT * FROM CINEMA;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT *
FROM CINEMA
WHERE ID%2=1 AND DESCRIPTION NOT IN ('BORING')
ORDER BY RATING DESC;

29 Number of comments per post

Table: Submissions

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| sub_id        | int      |
| parent_id     | int      |
+---------------+----------+
There is no primary key for this table, it may have duplicate rows.
Each row can be a post or comment on the post.
parent_id is null for posts.
parent_id for comments is sub_id for another post in the table.
 

Write an SQL query to find number of comments per each post.

Result table should contain post_id and its corresponding number_of_comments, 
and must be sorted by post_id in ascending order.

Submissions may contain duplicate comments. You should count the number of unique comments per post.

Submissions may contain duplicate posts. You should treat them as one post.

The query result format is in the following example:

Submissions table:
+---------+------------+
| sub_id  | parent_id  |
+---------+------------+
| 1       | Null       |
| 2       | Null       |
| 1       | Null       |
| 12      | Null       |
| 3       | 1          |
| 5       | 2          |
| 3       | 1          |
| 4       | 1          |
| 9       | 1          |
| 10      | 2          |
| 6       | 7          |
+---------+------------+

Result table:
+---------+--------------------+
| post_id | number_of_comments |
+---------+--------------------+
| 1       | 3                  |
| 2       | 2                  |
| 12      | 0                  |
+---------+--------------------+

The post with id 1 has three comments in the table with id 3, 4 and 9. The comment with id 3 is 
repeated in the table, we counted it only once.
The post with id 2 has two comments in the table with id 5 and 10.
The post with id 12 has no comments in the table.
The comment with id 6 is a comment on a deleted post with id 7 so we ignored it.

29.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS SUBMISSIONS;

CREATE TABLE IF NOT EXISTS SUBMISSIONS (SUB_ID INT,
 PARENT_ID INT);

TRUNCATE TABLE SUBMISSIONS;

INSERT INTO SUBMISSIONS VALUES 
("1", NULL),
("2", NULL),
("1", NULL),
("12", NULL),
("3", "1"),
("5", "2"),
("3", "1"),
("4", "1"),
("9", "1"),
("10", "2"),
("6", "7");


SELECT * FROM SUBMISSIONS;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT *
FROM SUBMISSIONS A LEFT JOIN SUBMISSIONS B ON
B.PARENT_ID = A.SUB_ID  
WHERE A.PARENT_ID IS NULL;

SELECT *
FROM SUBMISSIONS A LEFT JOIN SUBMISSIONS B ON 
B.PARENT_ID = A.SUB_ID  
WHERE A.PARENT_ID IS NULL
GROUP BY A.SUB_ID;

SELECT A.SUB_ID POST_ID,
    COUNT(DISTINCT B.SUB_ID) NUMBER_OF_COMMENTS
FROM SUBMISSIONS A LEFT JOIN SUBMISSIONS B ON 
B.PARENT_ID = A.SUB_ID  
WHERE A.PARENT_ID IS NULL
GROUP BY A.SUB_ID
ORDER BY POST_ID;

##################################################################
##                            ANSWER                            ##
##################################################################

# WRITE YOUR MYSQL QUERY STATEMENT BELOW
SELECT POST_ID, COUNT(SUB_ID) AS NUMBER_OF_COMMENTS
    FROM(
        SELECT DISTINCT A.SUB_ID AS POST_ID, B.SUB_ID AS SUB_ID
            FROM SUBMISSIONS A LEFT JOIN SUBMISSIONS B
            ON A.SUB_ID = B.PARENT_ID
            WHERE A.PARENT_ID IS NULL
    ) AS NUMBER
    GROUP BY POST_ID;

30 Product Sales Analysis 1

Write an SQL query that reports all product names of the products in the Sales table along with their selling year and price.

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(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.
 

For example:

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

Result table:
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

30.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS SALES;
CREATE TABLE IF NOT EXISTS SALES
 (SALE_ID INT,
 PRODUCT_ID INT , YEAR INT , 
 QUANTITY INT ,PRICE INT );
TRUNCATE TABLE SALES;
INSERT INTO SALES VALUES 
("1" , "100 " , "2008" , "10" , "5000"),
("2" , "100 " , "2009" , "12" , "5000"),
("7" , "200 " , "2011" , "15" , "9000");
SELECT * FROM SALES;


DROP TABLE IF EXISTS PRODUCT;
CREATE TABLE IF NOT EXISTS PRODUCT
 (PRODUCT_ID INT,
PRODUCT_NAME VARCHAR(25));
TRUNCATE TABLE PRODUCT;
INSERT INTO PRODUCT VALUES 
("100" , "NOKIA"),
("200" , "APPLE"),
("300" , "SAMSUNG");
SELECT * FROM PRODUCT;

##################################################################
##                            ANSWER                            ##
##################################################################
SELECT P.PRODUCT_NAME, S.YEAR , S.PRICE
FROM SALES S JOIN PRODUCT  P ON S.PRODUCT_ID = P.PRODUCT_ID

31 Product Sales Analysis 2

Write an SQL query that reports the total quantity sold for every product id.

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(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.
 

For example:

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

Result table:
Result table:
+--------------+----------------+
| product_id   | total_quantity |
+--------------+----------------+
| 100          | 22             |
| 200          | 15             |
+--------------+----------------+

31.1 Solution

USE SQL_LEEDCODE;

DROP TABLE IF EXISTS SALES;
CREATE TABLE IF NOT EXISTS SALES
 (SALE_ID INT,
 PRODUCT_ID INT , YEAR INT , 
 QUANTITY INT ,PRICE INT );
TRUNCATE TABLE SALES;
INSERT INTO SALES VALUES 
("1" , "100 " , "2008" , "10" , "5000"),
("2" , "100 " , "2009" , "12" , "5000"),
("7" , "200 " , "2011" , "15" , "9000");
SELECT * FROM SALES;


DROP TABLE IF EXISTS PRODUCT;
CREATE TABLE IF NOT EXISTS PRODUCT
 (PRODUCT_ID INT,
PRODUCT_NAME VARCHAR(25));
TRUNCATE TABLE PRODUCT;
INSERT INTO PRODUCT VALUES 
("100" , "NOKIA"),
("200" , "APPLE"),
("300" , "SAMSUNG");
SELECT * FROM PRODUCT;

##################################################################
##                            ANSWER                            ##
##################################################################

SELECT PRODUCT_ID , SUM(QUANTITY)  AS TOTAL_QUANTITY
FROM SALES
GROUP BY PRODUCT_ID

32 Product Sales Analysis 3

Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold.

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(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.
 

For example:

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

Result table:
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

32.1 Solution

USE SQL_LEEDCODE;
USE SQL_LEEDCODE;

DROP TABLE IF EXISTS SALES;
CREATE TABLE IF NOT EXISTS SALES
 (SALE_ID INT,
 PRODUCT_ID INT , YEAR INT , 
 QUANTITY INT ,PRICE INT );
TRUNCATE TABLE SALES;
INSERT INTO SALES VALUES 
("1" , "100 " , "2008" , "10" , "5000"),
("2" , "100 " , "2009" , "12" , "5000"),
("7" , "200 " , "2011" , "15" , "9000");
SELECT * FROM SALES;


DROP TABLE IF EXISTS PRODUCT;
CREATE TABLE IF NOT EXISTS PRODUCT
 (PRODUCT_ID INT,
PRODUCT_NAME VARCHAR(25));
TRUNCATE TABLE PRODUCT;
INSERT INTO PRODUCT VALUES 
("100" , "NOKIA"),
("200" , "APPLE"),
("300" , "SAMSUNG");
SELECT * FROM PRODUCT;

##################################################################
##                            ANSWER                            ##
##################################################################

SELECT  P.PRODUCT_ID , YEAR AS FIRST_YEAR , 
 QUANTITY, PRICE
FROM SALES S LEFT JOIN PRODUCT P ON P.PRODUCT_ID = S.PRODUCT_ID
GROUP BY P.PRODUCT_NAME;


# WRITE YOUR MYSQL QUERY STATEMENT BELOW
SELECT PRODUCT_ID, YEAR AS FIRST_YEAR, QUANTITY, PRICE FROM SALES
    WHERE (PRODUCT_ID, YEAR) IN (
        SELECT PRODUCT_ID, MIN(YEAR) FROM SALES
        GROUP BY PRODUCT_ID);