SQL_50_LeetCode_QnA.sql

use leetcode;

#https://leetcode.com/problems/recyclable-and-low-fat-products
select product_id
from Products
where low_fats = 'Y'
  and recyclable = 'Y';

#https://leetcode.com/problems/find-customer-referee
select name
from Customer
where referee_id is null
   or referee_id <> 2;

#https://leetcode.com/problems/big-countries
select name, population, area
from World
where area >= 3000000
   or population >= 25000000;

#https://leetcode.com/problems/article-views-i/
select author_id id
from Views
where author_id = viewer_id
group by author_id
order by author_id;

#https://leetcode.com/problems/invalid-tweets
select tweet_id
from Tweets
where length(content) > 15


#Basic Joins
#https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier
select unique_id, name
from Employees
         left join EmployeeUNI on Employees.id = EmployeeUNI.id;

#https://leetcode.com/problems/product-sales-analysis-i/description
select product_name, year, price
from Sales
         left join Product P on Sales.product_id = P.product_id;


select *
from Product
         left join Sales S on Product.product_id = S.product_id;


#https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions
select customer_id, count(*) count_no_trans
from Visits
         left join Transactions T on Visits.visit_id = T.visit_id
where transaction_id is null
group by customer_id
order by count_no_trans desc;

#https://leetcode.com/problems/rising-temperature/?envType=study-plan-v2&envId=top-sql-50
#date_sub(b.recordDate,INTERVAL 1 DAY)
select *, date_sub(b.recordDate, INTERVAL 1 DAY)
from Weather a
         join Weather b
where a.recordDate = b.recordDate
#and a.temperature > b.temperature;
SELECT *, Lag(recordDate, 2, '1999-09-01') OVER (ORDER BY recordDate ASC) AS EndDate
FROM Weather;



select *
from Activity;


select a.machine_id, round(avg(b.timestamp - a.timestamp), 3) processing_time
from Activity a
         join Activity b on a.machine_id = b.machine_id and a.process_id = b.process_id
where a.activity_type = 'start'
  and b.activity_type = 'end'
group by a.machine_id;


WITH ste AS (SELECT * FROM Activity WHERE activity_type = 'start'),
     ente AS (SELECT * FROM Activity WHERE activity_type = 'end')
SELECT ste.machine_id, ROUND(AVG(ente.timestamp - ste.timestamp), 3) Processing_time
FROM ste
         JOIN ente ON ste.machine_id = ente.machine_id
GROUP BY ste.machine_id;


select name, bonus
from Bonus
         right join Employee E on Bonus.empId = E.empId
where bonus < 1000
   or bonus is null;
#student_id | student_name | subject_name | attended_exams

select Students.student_name, Subjects.subject_name, Students.student_id, count(e.student_id)
from Students
         cross join Subjects
         left join Examinations E on Students.student_id = E.student_id and E.subject_name = Subjects.subject_name
group by Students.student_name, Subjects.subject_name, Students.student_id
ORDER BY student_id, subject_name;

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 ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;


with x as (select EmployeeUNI.id as id1, Employees.id * 2 as id2
           from Employees
                    left join EmployeeUNI on Employees.id = EmployeeUNI.id)

select id1, id2, coalesce(id1, id2, 99999999) result
from x;



select S.student_id, s.student_name, sb.subject_name, count(e.student_id) as attended_exams
from Students s
         cross join Subjects sb
         left join Examinations E on s.student_id = E.student_id AND sb.subject_name = e.subject_name
group by S.student_id, s.student_name, sb.subject_name
ORDER BY s.student_id, sb.subject_name;


select name
from employee
where id in (select managerId from Employee group by managerId having count(managerId) >= 5);


select customer_id, count(*)
from Visits
group by customer_id
having count(*) > 1;

select distinct z.customer_id
from (select *, row_number() over (Partition by customer_id order by customer_id) as r from Visits) z
where r > 1;


######################################## START ##########################################################


Create table If Not Exists Signups (user_id int, time_stamp datetime)
Create table If Not Exists Confirmations (user_id int, time_stamp datetime, action ENUM('confirmed','timeout'))
Truncate table Signups
insert into Signups (user_id, time_stamp) values ('3', '2020-03-21 10:16:13')
insert into Signups (user_id, time_stamp) values ('7', '2020-01-04 13:57:59')
insert into Signups (user_id, time_stamp) values ('2', '2020-07-29 23:09:44')
insert into Signups (user_id, time_stamp) values ('6', '2020-12-09 10:39:37')
Truncate table Confirmations
insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-01-06 03:30:46', 'timeout')
insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-07-14 14:00:00', 'timeout')
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-12 11:57:29', 'confirmed')
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-13 12:58:28', 'confirmed')
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-14 13:59:27', 'confirmed')
insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-01-22 00:00:00', 'confirmed')
insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-02-28 23:59:59', 'timeout')

select * from Confirmations;
select * from Signups;

select Signups.user_id,round(coalesce(sum(IF(c.action = 'timeout', 0, 1))/count(C.time_stamp),0 ),2) confirmation_rate from Signups left join Confirmations C on C.user_id=Signups.user_id
group by Signups.user_id order by confirmation_rate;
######################################## END ############################################################


######################################## START ##########################################################
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')######################################## END ############################################################


select *
from cinema;


# Write a solution 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.

select * from cinema where id%2<>0 and description<>'boring' order by rating desc;

######################################## START ##########################################################

Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int)
Create table If Not Exists UnitsSold (product_id int, purchase_date date, units int)
Truncate table Prices
insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-02-17', '2019-02-28', '5')
insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-03-01', '2019-03-22', '20')
insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-01', '2019-02-20', '15')
insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-21', '2019-03-31', '30')
Truncate table UnitsSold
insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-02-25', '100')
insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-03-01', '15')
insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-02-10', '200')
insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-03-22', '30')

select Prices.product_id,coalesce(round(sum(units*price)/sum(units),2),0) average_price
from Prices left join UnitsSold US on Prices.product_id = US.product_id
and purchase_date between start_date and end_date
group by Prices.product_id;



######################################## END ############################################################

Create table If Not Exists Project (project_id int, employee_id int)
Create table If Not Exists Employee1 (employee_id int, name varchar(10), experience_years int)
Truncate table Project
insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')
Truncate table Employee1;
insert into Employee1 (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee1 (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee1 (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee1 (employee_id, name, experience_years) values ('4', 'Doe', '2')

#Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

select P.project_id,round(sum(experience_years)/count(P.employee_id),2) average_years
from Employee1 right join  Project P on Employee1.employee_id = P.employee_id
group by P.project_id


######################################## START ##########################################################
#https://leetcode.com/problems/percentage-of-users-attended-a-contest/?envType=study-plan-v2&envId=top-sql-50
Create table If Not Exists Users (user_id int, user_name varchar(20))
Create table If Not Exists Register (contest_id int, user_id int)
Truncate table Users
insert into Users (user_id, user_name) values ('6', 'Alice')
insert into Users (user_id, user_name) values ('2', 'Bob')
insert into Users (user_id, user_name) values ('7', 'Alex')
Truncate table Register
insert into Register (contest_id, user_id) values ('215', '6')
insert into Register (contest_id, user_id) values ('209', '2')
insert into Register (contest_id, user_id) values ('208', '2')
insert into Register (contest_id, user_id) values ('210', '6')
insert into Register (contest_id, user_id) values ('208', '6')
insert into Register (contest_id, user_id) values ('209', '7')
insert into Register (contest_id, user_id) values ('209', '6')
insert into Register (contest_id, user_id) values ('215', '7')
insert into Register (contest_id, user_id) values ('208', '7')
insert into Register (contest_id, user_id) values ('210', '2')
insert into Register (contest_id, user_id) values ('207', '2')
insert into Register (contest_id, user_id) values ('210', '7')


select contest_id, round(count(Register.user_id)/(select count(user_id) from Users) *100,2) percentage
from Register right join Users U on Register.user_id = U.user_id
where contest_id is not null #useless edge case
group by contest_id order by percentage desc,contest_id asc

######################################## END ############################################################



######################################## START ##########################################################
#https://leetcode.com/problems/queries-quality-and-percentage/?envType=study-plan-v2&envId=top-sql-50

Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int)
Truncate table Queries
insert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5')
insert into Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', '2', '5')
insert into Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', '1')
insert into Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', '5', '2')
insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '3', '3')
insert into Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', '7', '4')


select query_name,round(sum(rating/position)/count(*),2) quality,
       round((sum(rating < 3)/count(*))*100,2) poor_query_percentage
from Queries group by query_name


######################################## END ############################################################



######################################## START ##########################################################
#https://leetcode.com/problems/monthly-transactions-i/?envType=study-plan-v2&envId=top-sql-50
Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
Truncate table Transactions
insert into Transactions (id, country, state, amount, trans_date) values ('121', 'US', 'approved', '1000', '2018-12-18')
insert into Transactions (id, country, state, amount, trans_date) values ('122', 'US', 'declined', '2000', '2018-12-19')
insert into Transactions (id, country, state, amount, trans_date) values ('123', 'US', 'approved', '2000', '2019-01-01')
insert into Transactions (id, country, state, amount, trans_date) values ('124', 'DE', 'approved', '2000', '2019-01-07')

select *
from Transactions;

######################################## END ############################################################





######################################## START ##########################################################

######################################## END ############################################################





######################################## START ##########################################################

######################################## END ############################################################




######################################## START ##########################################################

######################################## END ############################################################





######################################## START ##########################################################

######################################## END ############################################################





######################################## START ##########################################################

######################################## END ############################################################