SQL_leetcode, Sql Server

SQL_leetcode

CREATE TABLE Table1
    ([player_id] int, [device_id] int, [event_date] datetime, [games_played] int)
;
    
INSERT INTO Table1
    ([player_id], [device_id], [event_date], [games_played])
VALUES
    (1, 2, '2016-03-01 00:00:00', 5),
    (1, 2, '2016-03-02 00:00:00', 6),
    (2, 3, '2017-06-25 00:00:00', 1),
    (3, 1, '2016-03-02 00:00:00', 0),
    (3, 4, '2018-07-03 00:00:00', 5)
;

CREATE TABLE Employee
    ([Id] int, [Company] varchar(1), [Salary] int)
;
    
INSERT INTO Employee
    ([Id], [Company], [Salary])
VALUES
    (1, 'A', 2341),
    (2, 'A', 341),
    (3, 'A', 15),
    (4, 'A', 15314),
    (5, 'A', 451),
    (6, 'A', 513),
    (7, 'B', 15),
    (8, 'B', 13),
    (9, 'B', 1154),
    (10, 'B', 1345),
    (11, 'B', 1221),
    (12, 'B', 234),
    (13, 'C', 2345),
    (14, 'C', 2645),
    (15, 'C', 2645),
    (16, 'C', 2652),
    (17, 'C', 65)
;
/*
WITH t1 AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Salary) AS SalRank,
    COUNT(*) OVER(PARTITION BY Company) AS cnt
    FROM Employee)
SELECT Id, Company, Salary
FROM t1
--WHERE SalRank BETWEEN n/2 AND n/2+1
WHERE SalRank IN (CEILING(cnt/2.0),cnt/2+1)
*/
----------------------------------------------------------   570 -------------------------------------------
drop table employee
create table employee (Id int, Name varchar(10),Department varchar(2), ManagerId int);
 
 
insert into employee values(101,'John','A',null);
insert into employee values(102,'Dan','A',101);
insert into employee values(103,'James','A',101);
insert into employee values(104,'Amy','A',101);
insert into employee values(105,'Anne','A',101);
insert into employee values(106,'Ron','B',101);

/* write a SQL query that finds out managers with at least 5 direct report. 
For the above table, your SQL query should return:
+-------+
| Name  |
+-------+
| John  |
+-------+
*/
/*
WITH Manager AS (
    SELECT ManagerId, COUNT(Id) AS Num_reports
    FROM employee
    GROUP BY ManagerId
    HAVING ManagerId IS NOT NULL)
SELECT Name
FROM employee AS a INNER JOIN Manager AS b ON a.Id = b.ManagerId WHERE Num_reports=5;
*/
-------------------------------- 571 -------------------------
 
create Table numbers (number int,frequency int);
 
insert into numbers values (0,7);
insert into numbers values (1,1);
insert into numbers values (2,3);
insert into numbers values (3,1);

/*Write a query to find the median of all numbers and name the result as median.*/

/*
WITH t1 AS (
    SELECT *, SUM(frequency) OVER(ORDER BY number) AS cum_freq, SUM(frequency) OVER() AS total_freq
    FROM numbers)
SELECT number
FROM t1
WHERE cum_freq BETWEEN total_freq/2 AND total_freq/2+frequency;
*/
------------------------------- 574 --------------------------

create table candidate(id int,Name varchar(2));
 
insert into candidate values (1,'A');
insert into candidate values (2,'B');
insert into candidate values (3,'C');
insert into candidate values (4,'D');
insert into candidate values (5,'E');
 
CREATE TABLE Vote (id int,CandidateId int);
insert into Vote values(1,2);
insert into Vote values(2,4);
insert into Vote values(3,3);
insert into Vote values(4,2);
insert into Vote values(5,5);

/*
Write a sql to find the name of the winning candidate, the above example will return the winner B.
*/

/*
WITH t AS
(SELECT CandidateId, COUNT(*) AS votes FROM Vote GROUP BY CandidateId),
t1 AS
(SELECT *, RANK() OVER(ORDER BY votes DESC) AS rank_candidate FROM t)
SELECT Name
FROM candidate AS a INNER JOIN t1 ON a.id = t1.CandidateId
WHERE rank_candidate=1;
*/

----------------- 578 -------------------------

create table survey_log (uid int,action varchar(25),question_id int,answer_id int,q_num int,timestamp int);
 
insert into survey_log values(5,'show',285,null,1,123);
insert into survey_log values(5,'answer',285,124124,1,124);
insert into survey_log values(5,'show',369,null,2,125);
insert into survey_log values(5,'skip',369,null,2,126);

/*
WITH t1 AS
(
SELECT question_id, COUNT(*) AS show_cnt
FROM survey_log
    WHERE action='show'
GROUP BY question_id
),
t2 AS
(
SELECT question_id, COUNT(*) AS answer_cnt
FROM survey_log
    WHERE action='answer'
GROUP BY question_id
)

SELECT question_id
FROM(
SELECT a.question_id, RANK() OVER(ORDER BY CAST(CASE WHEN answer_cnt IS NULL THEN 0 ELSE answer_cnt END/show_cnt AS DECIMAL(4,2)) DESC) AS ansRateRank
FROM t1 AS a LEFT JOIN t2 AS b ON a.question_id = b.question_id
) t
WHERE ansRateRank=1
*/

---------------------------  579 ----------------

Create table employees2(Id int,[Month] int,salary int);

insert into employees2 values(1,1,20);
insert into employees2 values(2,1,20);
insert into employees2 values(1,2,30);
insert into employees2 values(2,2,30);
insert into employees2 values(3,2,40);
insert into employees2 values(1,3,40);
insert into employees2 values(3,3,60);
insert into employees2 values(1,4,60);
insert into employees2 values(3,4,70);

/*Write a SQL to get the cumulative sum of an employee's salary over a period of 3 months but exclude the most recent month.*/

/*
WITH t1 AS
(SELECT a.*
 FROM employees2 AS a INNER JOIN (SELECT Id, MAX(Month) AS most_recent_month FROM employees2 GROUP BY Id) AS b
 ON a.Id = b.Id
 WHERE a.Month < b.most_recent_month)

SELECT Id, Month, SUM(salary) OVER(PARTITION BY Id ORDER BY Month) AS Cum_Salary
FROM t1
*/

------------------ 580 -------------------
create table student
(
student_id int,
student_name varchar(10),
gender varchar(2),
dept_id int
);
 
insert into student values(1,'Jack','M',1);
insert into student values(2,'Jane','F',1);
insert into student values(3,'Mark','M',2);

create table department
(
dept_id int,
dept_name varchar(25)
);
 
insert into department values(1,'Engineering');
insert into department values(2,'Science');
insert into department values(3,'Law');

/*Write a query to print the respective department name and number of students majoring in each department 
for all departments in the department table (even ones with no current students).*/

/*
SELECT a.dept_id, SUM(CASE WHEN b.student_id IS NOt NULL THEN 1 ELSE 0 END) AS student_number
FROM department AS a LEFT JOIN  student AS b ON a.dept_id = b.dept_id
GROUP BY a.dept_id

------------------- 585 ------------------
create table insurance (PID int,TIV_2015 int,TIV_2016 int,LAT int,LON int);
 
insert into insurance values(1,10,5,10,10);
insert into insurance values(2,20,20,20,20);
insert into insurance values(3,10,30,20,20);
insert into insurance values(4,10,40,40,40);
/* Write a query to print the sum of all total investment values in 2016 (TIV_2016), to a scale of 2 decimal places, for all policy holders who meet the following criteria:

Have the same TIV_2015 value as one or more other policyholders.
Are not located in the same city as any other policyholder (i.e.: the (latitude, longitude) attribute pairs must be unique).*/

/*
WITH t1 AS
(SELECT LAT, LON, COUNT(PID) AS cnt_location
 FROM insurance
 GROUP BY LAT, LON
 HAVING COUNT(PID)=1),
 t2 AS
(SELECT TIV_2015, COUNT(PID) AS cnt_policy
 FROM insurance
 GROUP BY TIV_2015
 HAVING COUNT(PID)>1)
 
SELECT SUM(a.TIV_2016) TIV_2016
FROM insurance AS a INNER JOIN t1 ON a.LAT = t1.LAT AND a.LON = t1.LON
INNER JOIN t2 ON a.TIV_2015 = t2.TIV_2015;

----------------------- 601 ----------------------
Create table stadium (id int, visit_date DATE NULL, people int);
--Truncate table stadium
insert into stadium (id, visit_date, people) values (1, '2017-01-01', 10);
insert into stadium (id, visit_date, people) values (2, '2017-01-02', 109);
insert into stadium (id, visit_date, people) values (3, '2017-01-03', 150);
insert into stadium (id, visit_date, people) values (4, '2017-01-04', 99);
insert into stadium (id, visit_date, people) values (5, '2017-01-05', 145);
insert into stadium (id, visit_date, people) values (6, '2017-01-06', 1455);
insert into stadium (id, visit_date, people) values (7, '2017-01-07', 199);
insert into stadium (id, visit_date, people) values (8, '2017-01-08', 188);

/* Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).*/

/*
WITH t1 AS (
SELECT s1.*
FROM stadium AS s1, stadium AS s2, stadium AS s3
WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
AND DATEADD(day, 1, s1.visit_date) = s2.visit_date AND DATEADD(day, 1, s2.visit_date) = s3.visit_date),
t2 AS (
SELECT s2.*
FROM stadium AS s1, stadium AS s2, stadium AS s3
WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
AND DATEADD(day, 1, s1.visit_date) = s2.visit_date AND DATEADD(day, 1, s2.visit_date) = s3.visit_date),
t3 AS (
    SELECT s3.*
FROM stadium AS s1, stadium AS s2, stadium AS s3
WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
AND DATEADD(day, 1, s1.visit_date) = s2.visit_date AND DATEADD(day, 1, s2.visit_date) = s3.visit_date)
SELECT *
FROM t1
UNION
SELECT * 
FROM t2
UNION
SELECT *
FROM t3;
*/

/**** sol2 *******/
/*
select distinct day1.* 
from
stadium day1, stadium day2, stadium day3
where
day1.people >= 100 and day2.people >= 100 
and day3.people >= 100 and
((day1.id + 1 =  day2.id and day1.id + 2 = day3.id) or
(day1.id - 1 = day2.id and day1.id + 1 = day3.id) or
(day1.id - 2 = day2.id and day1.id - 1 = day3.id)) 
order by day1.id; 
*/

--------------------------- 602 ---------------------
create table request_accepted
(
requester_id int,
accepter_id int,
accept_date date
);
 
insert into request_accepted values(1,2,'2016-06-03');
insert into request_accepted values(1,3,'2016-06-08');
insert into request_accepted values(2,3,'2016-06-08');
insert into request_accepted values(3,4,'2016-06-09');

/* Write a query to find the the people who has most friends and the most friends number.  */
WITH t1 AS(
SELECT uid, SUM(friends) AS friends, RANK() OVER(ORDER BY SUM(friends) DESC) AS fRank
FROM(
SELECT requester_id AS uid, COUNT(accepter_id) AS friends
FROM request_accepted 
GROUP BY requester_id
UNION ALL
SELECT accepter_id AS uid, COUNT(requester_id) AS friends
FROM request_accepted
GROUP BY accepter_Id) t
GROUP BY uid)

SELECT uid
FROM t1
WHERE fRank=1;

/**** better sol **********/
select  top 1 id1, count(*) as num 
from 
(
select requester_id as id1, accepter_id as id2 from request_accepted
union all
select accepter_id as id1, requester_id as id2 from request_accepted
) as tpl
group by id1
order by num desc

---------------------- 608 ------------------
create table tree(id int,p_id int);
 
insert into tree values (1,null);
insert into tree values (2,1);
insert into tree values (3,1);
insert into tree values (4,2);
insert into tree values (5,2);


run \| edit \| history \| help	0