Leetcode SQL.txt

LeetCode:




Consecutive Numbers

with consecutive_num as (
select
case when (lead (num, 1,’-1’) over (order by id) = num and
lag(num,1,’-1’) over (order by id) = num)
then num end as ConsecutiveNums
from Logs)
select distinct ConsecutiveNums from
consecutive_num where ConsecutiveNums is not null

Duplicate Emails
with cte as (select email as Email,dense_rank() over (partition by email order by id) as rn from person) select distinct(Email) from cte where rn >=2;


Department Highest Salary:
with cte as (select d.name as Department , e.name as Employee,e.salary as Salary , dense_rank() over(partition by e.departmentId order by e.salary desc) as rn from employee e inner join department d on e.departmentId = d.id ) select Department,Employee,Salary from cte where rn = 1;

Customers Who Never Order

select c.name as Customers from customers c  where c.id not in(select customerId from orders);
select c.name as Customers from customers c left join orders o on c.id = o.customerId where o.customerId is null;
 select name as Customers from customers c where not exists (select * from orders o where o.customerId = c.id);

Department Top Three Salaries

with cte as (select d.name as Department , e.name as Employee , e.salary as Salary , dense_rank() over(partition by e.departmentId order by e.salary desc) as rn from employee e inner join department d on e.departmentId = d.id) select Department , Employee, Salary from cte where rn <= 3;


Rising Temperature

with cte as(select id ,temperature, lag(temperature,1) over(order by recordDate ) as nxtdaytemp ,lag(recordDate,1,recordDate) over (order by recordDate) as prev_date ,recordDate from weather ) select id from cte where nxtdaytemp < temperature and datediff(recordDate,prev_date) = 1 ;

Delete Duplicate Emails:

delete from person where id in (with cte as (select id,email,dense_rank() over (partition by email order by id) as rn from person) select id from cte where rn >= 2);



Trips and Users 

SELECT Request_at AS Day,
ROUND(SUM(IF(Status != 'completed', 1, 0)) /COUNT(Status) ,2) AS 'Cancellation Rate'
FROM Trips
WHERE Request_at >= "2013-10-01" AND Request_at <= "2013-10-03"
AND Client_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = "Yes")
AND Driver_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = "Yes")
GROUP BY Request_at

SELECT Request_at AS Day, 
round(sum(case when (status = "cancelled_by_driver" or status = "cancelled_by_client") then 1 else 0 end) / count(status),2) as 'Cancellation Rate'
FROM Trips
where Request_at between '2013-10-01' and '2013-10-03'
and client_id not in (select users_id from users where banned = 'Yes')
and driver_id not in (select users_id from users where banned = 'Yes')
group by Request_at;


SELECT Request_at AS Day, 
round(sum(case when (status != "completed") then 1 else 0 end) / count(status),2) as 'Cancellation Rate'
FROM Trips
where Request_at between '2013-10-01' and '2013-10-03'
and client_id not in (select users_id from users where banned = 'Yes')
and driver_id not in (select users_id from users where banned = 'Yes')
group by Request_at;


Game Play Analysis I
with cte as (select player_id ,min(event_date) as first_login from activity group by player_id)
select * from cte;

Find Customer Referee:
with cte as (select name from Customer where referee_id is null or referee_id != 2) select * from cte;

Customer Placing the Largest Number of Orders:
with cte as (select customer_number,count(order_number) as cnt from orders group by customer_number) select customer_number from cte where cnt in (select max(cnt) from cte);

Big Countries:
with cte as (select name ,population,area from world where area >= 3000000 or population >=25000000 ) select * from cte;

Classes More Than 5 Students
with cte as (select class , count(student) as cnt from courses group by class) select class from cte where cnt >=5;

Human Traffic of Stadium:
with cte as (select * , id - rank() over(order by id asc) as diff from stadium where people >= 100) ,cte2 as (select * ,count(*) over(partition by diff) as cnt from cte) select id,visit_date , people  from cte2 where cnt >=3;


Sales Person:
with cte as (select c.name as companyname  ,o.sales_id as order_sid from company c inner join orders o on c.com_id = o.com_id) , cte2 as (select s.name as name,ct.companyname as cname from salesperson s inner join cte ct on s.sales_id=ct.order_sid where ct.companyname ='RED') select name from salesperson where name not in (select name from cte2);

Tree Node:
select id , case when p_id is null then 'Root'
                 when id not in (select distinct(p_id) from tree where p_id is not null) then 'Leaf'
                 else 'Inner'
            end as Type
from tree;

Not Boring Movies
select * from Cinema where mod(id,2) = 1 and description != 'boring' order by rating desc;

Exchange Seats:
with cte as (select id ,student , lag(student,1,student) over (order by id) as prev_student,lead(student,1,student) over (order by id) as next_student from seat) select id , case when mod(id,2) = 0 then prev_student else next_student end as student from cte;

Swap Salary:
update salary set sex = case when sex='m' then 'f' else 'm' end
update salary set sex = if (sex='m','f','m')

Actors and Directors Who Cooperated At Least Three Times:
with cte as (select actor_id,director_id ,count(case when actor_id = director_id then 1 else 0 end ) as cnt from actordirector group by actor_id,director_id) select actor_id,director_id from cte where cnt >=3;

Sales Analysis III:
with cte as (select p.product_id,p.product_name from product p inner join sales s on p.product_id=s.product_id group by p.product_id,p.product_name having min(s.sale_date) >= '2019-01-01'and max(s.sale_date) <= '2019-03-31') select * from cte;


User Activity for the Past 30 Days I:

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


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

Market Analysis I:
select u.user_id as 'buyer_id',u.join_date as 'join_date',
count(o.order_date) as 'orders_in_2019' from users u left join orders o on u.user_id=o.buyer_id
and year(o.order_date) ='2019'
group by user_id

Reformat Department Table:

select id,
sum(case when month = 'Jan' then revenue else Null end) as 'Jan_Revenue',
sum(case when month = 'Feb' then revenue else Null end) as 'Feb_Revenue',
sum(case when month = 'Mar' then revenue else Null end) as 'Mar_Revenue',
sum(case when month = 'Apr' then revenue else Null end) as 'Apr_Revenue',
sum(case when month = 'May' then revenue else Null end) as 'May_Revenue',
sum(case when month = 'Jun' then revenue else Null end) as 'Jun_Revenue',
sum(case when month = 'Jul' then revenue else Null end) as 'Jul_Revenue',
sum(case when month = 'Aug' then revenue else Null end) as 'Aug_Revenue',
sum(case when month = 'Sep' then revenue else Null end) as 'Sep_Revenue',
sum(case when month = 'Oct' then revenue else Null end) as 'Oct_Revenue',
sum(case when month = 'Nov' then revenue else Null end) as 'Nov_Revenue',
sum(case when month = 'Dec' then revenue else Null end) as 'Dec_Revenue'
from department
group by id;

another approach:

select id,
SUM(IF(month='Jan',revenue,NULL))AS 'Jan_Revenue',
SUM(IF(month='Feb',revenue,NULL))AS 'Feb_Revenue',
SUM(IF(month='Mar',revenue,NULL))AS 'Mar_Revenue',
SUM(IF(month='Apr',revenue,NULL))AS 'Apr_Revenue',
SUM(IF(month='May',revenue,NULL))AS 'May_Revenue',
SUM(IF(month='Jun',revenue,NULL))AS 'Jun_Revenue',
SUM(IF(month='Jul',revenue,NULL))AS 'Jul_Revenue',
SUM(IF(month='Aug',revenue,NULL))AS 'Aug_Revenue',
SUM(IF(month='Sep',revenue,NULL))AS 'Sep_Revenue',
SUM(IF(month='Oct',revenue,NULL))AS 'Oct_Revenue',
SUM(IF(month='Nov',revenue,NULL))AS 'Nov_Revenue',
SUM(IF(month='Dec',revenue,NULL))AS 'Dec_Revenue'
from Department
group by id


Capital Gain/Loss:
with cte as (select stock_name, 
(sum(case when operation ='Sell' then price else 0 end) - sum(case when operation ='Buy' then price else 0 end)) as capital_gain_loss 
from stocks
group by stock_name) select * from cte;


Top Travellers:
with cte as (select u.name as name ,sum(r.distance) as travelled_distance from users u left join rides r on u.id=r.user_id group by u.id order by sum(r.distance) desc , name asc ) select name ,COALESCE(travelled_distance,0) as travelled_distance from cte;

Group Sold Products By The Date:
SELECT sell_date,
		COUNT(DISTINCT(product)) AS num_sold, 
		GROUP_CONCAT(DISTINCT product ORDER BY product ASC) AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date ASC

Patients With a Condition:
select * from patients where conditions like 'DIAB1%' or conditions like '% DIAB1%';

Customer Who Visited but Did Not Make Any Transactions:
select customer_id,count(visit_id) as count_no_trans from visits
where visit_id not in(select visit_id from transactions) group by customer_id;


Bank Account Summary II: 
with cte as (select u.name as name, sum(t.amount) as balance from users u left join transactions t on u.account = t.account group by u.account having sum(t.amount) > 10000) select * from cte;


Fix Names in a Table:
select user_id , concat(upper(substring(name,1,1)),lower(substring(name,2))) as name from users order by user_id;

Daily Leads and Partners:
select date_id,make_name ,count(distinct(lead_id)) as unique_leads,count(distinct(partner_id)) as unique_partners from dailysales group by date_id,make_name;

Find Followers Count:
select user_id ,count(follower_id) as followers_count from followers group by user_id order by user_id;


Find Total Time Spent by Each Employee:
select event_day as day , emp_id,sum(out_time) - sum(in_time) as total_time from employees group by event_day,emp_id;

Recyclable and Low Fat Products:
select product_id from products where low_fats = 'Y' and recyclable =  'Y';

Rearrange Products Table:
SELECT * FROM 
(
      SELECT product_id, 'store1' store, store1 price from Products
	  UNION
	  SELECT product_id, 'store2' store, store2 price from Products
	  UNION
	  SELECT product_id, 'store3' store, store3 price from Products
) a
WHERE price is not null;

Calculate Special Bonus:
select employee_id , case when mod(employee_id,2) !=0  and name not like 'M%' then salary else 0 end as bonus from employees order by employee_id;


The Latest Login in 2020:
select user_id,max(time_stamp) as last_stamp from logins where year(time_stamp) ='2020' group by user_id;
select user_id,max(time_stamp) as last_stamp from logins where year(time_stamp) like '2020%' group by user_id;


Employees With Missing Information:
select employee_id from employees where employee_id not in (select employee_id from salaries) union select employee_id from salaries where employee_id not in (select employee_id from employees)
order by employee_id;

SELECT ISNULL(s.employee_id, e.employee_id) AS employee_id  FROM Employees e
    FULL OUTER JOIN Salaries s ON s.employee_id = e.employee_id
    WHERE s.employee_id IS NULL OR e.employee_id IS NULL
    ORDER BY employee_id ASC;
    
    
    
    
Combine Two Tables:
select p.firstName as firstName , p.lastName as lastName , a.city as city , a.state as state from person p left join address a on p.personId = a.personId;



mysql> CREATE TABLE events (
    -> ID int,
    -> event varchar(255),
    -> YEAR INt,
    -> GOLD varchar(255),
    -> SILVER varchar(255),
    -> BRONZE varchar(255)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO events VALUES (1,'100m',2016, 'Amthhew Mcgarray','donald','barbara');
INSERT INTO events VALUES (2,'200m',2016, 'Nichole','Alvaro Eaton','janet Smith');
INSERT INTO events VALUES (3,'500m',2016, 'Charles','Nichole','Susana');
INSERT INTO events VALUES (4,'100m',2016, 'Ronald','maria','paula');
INSERT INTO events VALUES (5,'200m',2016, 'Alfred','carol','Steven');
INSERT INTO events VALUES (6,'500m',2016, 'Nichole','Alfred','Brandon');
INSERT INTO events VALUES (7,'100m',2016, 'Charles','Dennis','Susana');
INSERT INTO events VALUES (8,'200m',2016, 'Thomas','Dawn','catherine');
INSERT INTO events VALUES (9,'500m',2016, 'Thomas','Dennis','paula');
INSERT INTO events VALUES (10,'100m',2016, 'Charles','Dennis','Susana');
INSERT INTO events VALUES (11,'200m',2016, 'jessica','Donald','Stefeney');
INSERT INTO events VALUES (12,'500m',2016,'Thomas','Steven','Catherine');Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO events VALUES (2,'200m',2016, 'Nichole','Alvaro Eaton','janet Smith');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (3,'500m',2016, 'Charles','Nichole','Susana');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (4,'100m',2016, 'Ronald','maria','paula');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (5,'200m',2016, 'Alfred','carol','Steven');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (6,'500m',2016, 'Nichole','Alfred','Brandon');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (7,'100m',2016, 'Charles','Dennis','Susana');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (8,'200m',2016, 'Thomas','Dawn','catherine');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (9,'500m',2016, 'Thomas','Dennis','paula');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (10,'100m',2016, 'Charles','Dennis','Susana');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (11,'200m',2016, 'jessica','Donald','Stefeney');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO events VALUES (12,'500m',2016,'Thomas','Steven','Catherine');
Query OK, 1 row affected (0.01 sec)

mysql> select * from events;
+------+-------+------+------------------+--------------+-------------+
| ID   | event | YEAR | GOLD             | SILVER       | BRONZE      |
+------+-------+------+------------------+--------------+-------------+
|    1 | 100m  | 2016 | Amthhew Mcgarray | donald       | barbara     |
|    2 | 200m  | 2016 | Nichole          | Alvaro Eaton | janet Smith |
|    3 | 500m  | 2016 | Charles          | Nichole      | Susana      |
|    4 | 100m  | 2016 | Ronald           | maria        | paula       |
|    5 | 200m  | 2016 | Alfred           | carol        | Steven      |
|    6 | 500m  | 2016 | Nichole          | Alfred       | Brandon     |
|    7 | 100m  | 2016 | Charles          | Dennis       | Susana      |
|    8 | 200m  | 2016 | Thomas           | Dawn         | catherine   |
|    9 | 500m  | 2016 | Thomas           | Dennis       | paula       |
|   10 | 100m  | 2016 | Charles          | Dennis       | Susana      |
|   11 | 200m  | 2016 | jessica          | Donald       | Stefeney    |
|   12 | 500m  | 2016 | Thomas           | Steven       | Catherine   |
+------+-------+------+------------------+--------------+-------------+
12 rows in set (0.00 sec)

mysql> select gold as player_name , count(gold) as no_of_gold from events where gold not in( select silver from events union all select bronze from events ) group by gold;
+------------------+------------+
| player_name      | no_of_gold |
+------------------+------------+
| Amthhew Mcgarray |          1 |
| Charles          |          3 |
| Ronald           |          1 |
| Thomas           |          3 |
| jessica          |          1 |
+------------------+------------+
5 rows in set (0.01 sec)

Input Table PIVOT question

with cte as (select case when age between 18 and 60 then '18-60' else '61+' end as age_group,  case when score=10 then 1 else 0 end as good, case when score=15 then 1 else 0 end as exceptional, score from input) select age_group,sum(good) as good,sum(exceptional) as exceptional  from cte group by age_group;



Microsoft Question select all salary < 70000(budget)
mysql> with cte as (select experience , salary , sum(salary) over (partition by experience order by salary ) as new_sal from candidates) , senior as (select * from cte where experience ='Senior' and new_sal <= 70000) ,output as (s
elect * from cte where experience = 'Junior' and new_sal <=(select sum(salary) from senior) union all select * from
 senior ) select * from output
    -> ;
+------------+--------+---------+
| experience | salary | new_sal |
+------------+--------+---------+
| Junior     |  10000 |   10000 |
| Junior     |  15000 |   25000 |
| Senior     |  16000 |   16000 |
| Senior     |  20000 |   36000 |
+------------+--------+---------+
4 rows in set (0.01 sec)