2021-03-08_LeetCodeSQL, Sql Server

Language: Layout:

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);
/* Write a query to print the node id and the type of the node. Sort your output by the node id. The result for the above sample is:*/

/*
SELECT id, 'leaf' AS type
FROM tree
WHERE p_id IS NOT NULL
AND id NOT IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL)
UNION ALL
SELECT id, 'root'
FROM tree
WHERE p_id IS NULL
UNION ALL
SELECT id, 'inner' AS type
FROM tree 
WHERE p_id IS NOT NULL
AND id IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL)
*/
--------------------------------------------------------------------------
/**** COUNT() automatically excludes NULL *****/

/*
SELECT id, COUNT(p_id)
FROM tree
GROUP BY id;
*/

--  	id	(No column name)
-- 1	1	0
-- 2	2	1
-- 3	3	1
-- 4	4	1
-- 5	5	1

-------------------------------------------------------------------------

------------ 614 --------------
--drop table if exists follow;
Create table follow (followee varchar(255), follower varchar(255));
--Truncate table follow;
insert into follow (followee, follower) values ('A', 'B');
insert into follow (followee, follower) values ('B', 'C');
insert into follow (followee, follower) values ('B', 'D');
insert into follow (followee, follower) values ('D', 'E');

/*
SELECT follower, n
FROM follow AS a INNER JOIN (SELECT followee, COUNT(follower) AS n FROM follow GROUP BY followee) AS b
ON a.follower = b.followee;
*/

------------  615 ---------------
drop table employee;
Create table salary(id int,employee_id int,amount int,pay_date date);
Create table employee(employee_id int,department_id int);
 
insert into salary values(1,2,9000,'2017-03-31');
insert into salary values(2,2,60000,'2016-03-31');
insert into salary values(3,3,10000,'2017-03-31');
insert into salary values(4,1,7000,'2017-02-28');
insert into salary values(5,2,6000,'2017-02-28');
insert into salary values(6,3,8000,'2017-02-28');
 
insert into employee values(1,1);
insert into employee values(2,2);
insert into employee values(3,2);

/* Given two tables as below, 
 write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department 
 to the company's average salary */

/*
SELECT pay_date,
       department_id, 
       CASE WHEN avg_dept>avg_company THEN 'higher'
            WHEN avg_dept = avg_company THEN 'same'
            WHEN avg_dept < avg_company THEN 'lower'
            ELSE 'error'
            END AS comparison

FROM
(
SELECT pay_date,
       e.department_id, 
       AVG(amount) OVER(PARTITION BY pay_date, department_id) AS avg_dept,
       AVG(amount) OVER(PARTITION BY pay_date) AS avg_company
 FROM salary AS s INNER JOIN employee AS e ON s.employee_id = e.employee_id

) AS t

*/
----------------------- 618 ----------------------------

drop table student;
Create table student (name varchar(50), continent varchar(7));
--Truncate table student;
 
insert into student (name, continent) values ('Jane', 'America');
insert into student (name, continent) values ('Pascal', 'Europe');
insert into student (name, continent) values ('Xi', 'Asia');
insert into student (name, continent) values ('Jack', 'America');

/*
WITH t0 AS(
SELECT name AS America, ROW_NUMBER() OVER(ORDER BY name) AS n
FROM student
WHERE continent='America'),
t1 AS (SELECT name AS Asia, ROW_NUMBER() OVER(ORDER BY name) AS n
 FROM student
 WHERE continent='Asia'),
t2 AS (SELECT name AS Europe, ROW_NUMBER() OVER(ORDER BY name) AS n
 FROM student
 WHERE continent='Europe')
SELECT America, Asia, Europe
FROM t0 LEFT JOIN t1 ON t0.n = t1.n
LEFT JOIN t2 ON t0.n = t2.n
*/
-------------------- 626 ----------------------
--drop table cinema;
Create table cinema (
    seat_id int primary key --auto_increment
    , 
    free int);
--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 a.seat_id
FROM cinema AS a, cinema AS b
WHERE (a.seat_id = b.seat_id+1 OR b.seat_id = a.seat_id+1) AND a.free = 1 AND b.free=1
GROUP BY a.seat_id
/*
UNION
SELECT b.seat_id
FROM cinema AS a, cinema AS b
WHERE a.seat_id = b.seat_id+1 --OR b.seat_id = a.seat_id+1
*/

-------------- 1045 ---------------

create table Customer(customer_id int, product_key int);
insert into Customer(customer_id,product_key)
values
(1,5),
(2,6),
(3,5),
(3,6),
(1,6);
create table Product(product_key int);
insert into Product(product_key)
values
(5),
(6);
/*Write an SQL query for a report that provides the customer ids from the Customer table that bought all the products in the Product table.
*/
/*
SELECT a.customer_id
FROM Customer AS a INNER JOIN Product AS b ON a.product_key = b.product_key
GROUP BY customer_id
HAVING COUNT(a.product_key) = (SELECT COUNT(product_key) FROM Product)
*/

--------------------- 1070 --------------
drop table Product
Create table Sales(sale_id int,product_id int,year int,quantity int,price int);
Create table Product(product_id int,product_name varchar(20));
 
insert into Sales values(1,100,2008,10,5000);
insert into Sales values(2,100,2009,12,5000);
insert into Sales values(7,200,2011,15,9000);
 
 
insert into Product values(100,'Nokia');
insert into Product values(200,'Apple');
insert into Product values(300,'Samsung');

/*Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold.*/
/*
SELECT product_id, year, quantity, price
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY year) AS product_row FROM Sales) AS t
WHERE product_row=1
*/

---------------------- 1077 --------------------

-- drop table Project
drop table Employee
Create table Project (project_id int, employee_id int)
Create table Employee (employee_id int, name varchar(20), experience_years int)
 
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')
 
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')

/*Write an SQL query that reports the most experienced employees in each project. 
 In case of a tie, report all employees with the maximum number of experience years.*/
 
SELECT project_id, name
FROM(
 SELECT project_id, a.employee_id, name, experience_years, RANK() OVER(PARTITION By project_id ORDER BY experience_years DESC) AS exp_rank
 FROM Project AS a INNER JOIN Employee As b ON a.employee_id = b.employee_id) AS t
WHERE exp_rank=1

------------------ 1097 ----------------

CREATE TABLE Activity
    ([player_id] int, [device_id] int, [event_date] datetime, [games_played] int)
;
    
INSERT INTO Activity
    ([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-01 00:00:00', 0),
    (3, 4, '2016-07-03 00:00:00', 5)
;

/* Write an SQL query that reports for each install date, the number of players that installed the game on that day 
and the day 1 retention.*/

WITH installation AS
(SELECT player_id, MIN(event_date) AS install_date
 FROM Activity
 GROUP BY player_id)
SELECT a.install_date, COUNT(DISTINCT a.player_id) AS installs, SUM(CASE WHEN b.event_date=a.install_date+1 THEN 1.00 ELSE 0 END)/COUNT(DISTINCT a.player_id) AS retention
FROM installation AS a 
LEFT JOIN Activity AS b
ON a.player_id = b.player_id
GROUP BY a.install_date;

------------------------------ 1098 ----------------------

CREATE TABLE Books
    ([book_id] int, [name] varchar(18), [available_from] datetime)
;
    
INSERT INTO Books
    ([book_id], [name], [available_from])
VALUES
    (1, '“Kalila And Demna”', '2010-01-01 00:00:00'),
    (2, '“28 Letters”', '2012-05-12 00:00:00'),
    (3, '“The Hobbit”', '2019-06-10 00:00:00'),
    (4, '“13 Reasons Why”', '2019-06-01 00:00:00'),
    (5, '“The Hunger Games”', '2008-09-21 00:00:00')
;

CREATE TABLE Orders
    ([order_id] int, [book_id] int, [quantity] int, [dispatch_date] datetime)
;
    
INSERT INTO Orders
    ([order_id], [book_id], [quantity], [dispatch_date])
VALUES
    (1, 1, 2, '2018-07-26 00:00:00'),
    (2, 1, 1, '2018-11-05 00:00:00'),
    (3, 3, 8, '2019-06-11 00:00:00'),
    (4, 4, 6, '2019-06-05 00:00:00'),
    (5, 4, 5, '2019-06-20 00:00:00'),
    (6, 5, 9, '2009-02-02 00:00:00'),
    (7, 5, 8, '2010-04-13 00:00:00')
;

/* 你需要写一段 SQL 命令，筛选出过去一年中订单总量 少于10本 的 书籍 。
注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。
*/

/*
SELECT *
FROM(
SELECT a.book_id, 
      name,
      SUM(CASE WHEN dispatch_date>=DATEADD(year, -1, '2019-06-23')
               THEN quantity ELSE 0 END) AS tot_quantity 
FROM Books AS a LEFT JOIN Orders AS b
ON a.book_id = b.book_id
WHERE available_from <=DATEADD(month,-1,'2019-06-23') 
GROUP BY a.book_id, name) t
WHERE tot_quantity<10
*/

-----------------  1107 --------------------
create table Traffic(
user_id int,
activity varchar(50),
activity_date date 
);
insert into Traffic values(1, 'login', '2019-05-01');
insert into Traffic values(1, 'homepage', '2019-05-01');
insert into Traffic values(1, 'logout', '2019-05-01');
insert into Traffic values(2, 'login', '2019-06-21');
insert into Traffic values(2, 'logout', '2019-06-21');
insert into Traffic values(3, 'login', '2019-01-01');
insert into Traffic values(3, 'jobs', '2019-01-01');
insert into Traffic values(3, 'logout', '2019-01-01');
insert into Traffic values(4, 'login', '2019-06-21');
insert into Traffic values(4, 'groups', '2019-06-21');
insert into Traffic values(4, 'logout', '2019-06-21');
insert into Traffic values(5, 'login', '2019-03-01');
insert into Traffic values(5, 'logout', '2019-03-01');
insert into Traffic values(5, 'login', '2019-06-21');
insert into Traffic values(5, 'logout', '2019-06-21');

/*Write an SQL query that reports for every date within at most 90 days from today, 
the number of users that logged in for the first time on that date. Assume today is 2019-06-30.*/

SELECT activity_date, COUNT(DISTINCT user_id) AS users
FROM
(SELECT user_id, activity_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY activity_date) AS user_rank
FROM Traffic) AS t
WHERE activity_date>=DATEADD(day, -90, '2019-06-30')
AND user_rank=1
GROUP BY activity_date

---------------------- !!!!!!!!!!! hard fist !!!!!!!!!!!------------

-------------- 1127  ------------------
CREATE TABLE Spending (
  user_id INT,
  spend_date DATE,
  platform VARCHAR(50),
  amount INT
) ;

INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (1, '2019-07-01', 'mobile', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (1, '2019-07-01', 'desktop', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (2, '2019-07-01', 'mobile', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (2, '2019-07-02', 'mobile', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (3, '2019-07-01', 'desktop', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (3, '2019-07-02', 'desktop', 100) ; 
INSERT INTO Spending ( user_id, spend_date, platform, amount ) VALUES (3, '2019-07-03', 'desktop', 100) ;

/*Write an SQL query to find the total number of users and the total amount spent using mobile only, 
 desktop only and both mobile and desktop together for each date.*/

/*
WITH m AS
(SELECT spend_date, user_id
FROM Spending
WHERE platform = 'mobile'),
d AS
(SELECT spend_date, user_id
 FROM Spending
 WHERE platform = 'desktop'),
platform_cat AS
(SELECT CASE WHEN m.spend_date IS NOT NULL THEN m.spend_date ELSE d.spend_date END AS spend_date, 
        CASE WHEN m.user_id IS NOt NULL THEN m.user_id ELSE d.user_id END AS user_id, 
        CASE WHEN m.user_id IS NULL THEN 'desktop'
             WHEN d.user_id IS NULL THEN 'mobile'
        ELSE 'both' END AS platform
 FROM m FULL OUTER JOIN d ON m.user_id = d.user_id)

SELECT spend_date, platform, COUNT(DISTINCT user_id)  AS users, SUM(amount) AS tot_amount
FROM(
SELECT a.spend_date, a.user_id, a.platform, SUM(amount) AS amount
FROM platform_cat AS a INNER JOIN spending AS b ON a.spend_date = b.spend_date AND a.user_id = b.user_id
GROUP BY a.spend_date, a.user_id, a.platform) t
GROUP BY spend_date, platform
ORDER BY spend_date, platform
*/
------------------ 1194 ---------------

CREATE TABLE Players
    ([player_id] int, [group_id] int)
;
    
INSERT INTO Players
    ([player_id], [group_id])
VALUES
    (15, 1),
    (25, 1),
    (30, 1),
    (45, 1),
    (10, 2),
    (35, 2),
    (50, 2),
    (20, 3),
    (40, 3)
;

CREATE TABLE Matches
    ([match_id] int, [first_player] int, [second_player] int, [first_score] int, [second_score] int)
;
    
INSERT INTO Matches
    ([match_id], [first_player], [second_player], [first_score], [second_score])
VALUES
    (1, 15, 45, 3, 0),
    (2, 30, 25, 1, 2),
    (3, 30, 15, 2, 0),
    (4, 40, 20, 5, 2),
    (5, 35, 50, 1, 1)
;

WITH t AS
(SELECT CASE WHEN first_score > second_score THEN first_player
             WHEN first_score < second_score THEN second_player
             WHEN first_score = second_score AND first_player<second_player THEN first_player
             WHEN first_score = second_score AND first_player>second_player THEN second_player END AS winner,
 CASE WHEN first_score > second_score THEN first_score
             WHEN first_score < second_score THEN second_score
             WHEN first_score = second_score AND first_player<second_player THEN first_score
             WHEN first_score = second_score AND first_player>second_player THEN second_score END AS winning_score
 FROM Matches)

SELECT group_id, winner, winning_score
FROM(
SELECT group_id, winner, SUM(winning_score) AS winning_score, RANK() OVER(PARTITION BY group_id ORDER BY SUM(winning_score) DESC) AS group_rank 
FROM t AS a INNER JOIN Players AS b ON a.winner = b.player_id
    GROUP BY group_id, winner) AS t1
WHERE group_rank=1

---------------------------- 1225 -----------------------

CREATE TABLE Failed
    ([fail_date] datetime)
;
    
INSERT INTO Failed
    ([fail_date])
VALUES
    ('2018-12-28 00:00:00'),
    ('2018-12-29 00:00:00'),
    ('2019-01-04 00:00:00'),
    ('2019-01-05 00:00:00')
;

CREATE TABLE Succeeded
    ([success_date] datetime)
;
    
INSERT INTO Succeeded
    ([success_date])
VALUES
    ('2018-12-30 00:00:00'),
    ('2018-12-31 00:00:00'),
    ('2019-01-01 00:00:00'),
    ('2019-01-02 00:00:00'),
    ('2019-01-03 00:00:00'),
    ('2019-01-06 00:00:00')
;
/*
Write an SQL query to generate a report of period_state for each continuous interval of days in the period from 2019-01-01 to 2019-12-31.
period_state is 'failed' if tasks in this interval failed or 'succeeded' if tasks in this interval succeeded. Interval of days are retrieved as start_date and end_date.
Order result by start_date.
*/

/*
WITH t AS
(SELECT fail_date AS date, 'failed' AS status
 FROM Failed
 UNION ALL
 SELECT success_date AS date, 'success' AS status
 FROM Succeeded)
SELECT status, MIN(date) AS start_date, MAX(date) AS end_date
FROM (SELECT status, date, RANK() OVER(PARTITION BY status ORDER BY date) AS ranking, RANK() OVER(ORDER BY date) AS row_number
FROM t) AS t1
GROUP BY status, ranking-row_number
ORDER BY MIN(date)

---------------------------- 1336 ----------------------------------

CREATE TABLE Transactions
    ([user_id] int, [transaction_date] datetime, [amount] int)
;
    
INSERT INTO Transactions
    ([user_id], [transaction_date], [amount])
VALUES
    (1, '2020-01-02 00:00:00', 120),
    (2, '2020-01-03 00:00:00', 22),
    (7, '2020-01-11 00:00:00', 232),
    (1, '2020-01-04 00:00:00', 7),
    (9, '2020-01-25 00:00:00', 33),
    (9, '2020-01-25 00:00:00', 66),
    (8, '2020-01-28 00:00:00', 1),
    (9, '2020-01-25 00:00:00', 9)
;

CREATE TABLE Visits
    ([user_id] int, [visit_date] datetime)
;
    
INSERT INTO Visits
    ([user_id], [visit_date])
VALUES
    (1, '2020-01-01 00:00:00'),
    (2, '2020-01-02 00:00:00'),
    (12, '2020-01-01 00:00:00'),
    (19, '2020-01-03 00:00:00'),
    (1, '2020-01-02 00:00:00'),
    (2, '2020-01-03 00:00:00'),
    (1, '2020-01-04 00:00:00'),
    (7, '2020-01-11 00:00:00'),
    (9, '2020-01-25 00:00:00'),
    (8, '2020-01-28 00:00:00')
;

/* Write an SQL query to find how many users visited the bank and didn't do any transactions, 
how many visited the bank and did one transaction and so on.

The result table will contain two columns:

transactions_count which is the number of transactions done in one visit.

visits_count which is the corresponding number of users who did transactions_count in one visit to the bank.

transactions_count should take all values from 0 to max(transactions_count) done by one or more users.*/

WITH Transaction_cnt AS
(SELECT transaction_date, user_id, COUNT(amount) AS transactions_count
 FROM Transactions
 GROUP BY transaction_date, user_id),
rn AS 
(SELECT ROW_NUMBER() OVER(ORDER BY transaction_date) AS k
 FROM Transactions
 UNION ALL
 SELECT 0)

SELECT k, COUNT(user_id) AS users
FROM
(SELECT a.visit_date, a.user_id, CASE WHEN transactions_count IS NULL THEN 0 ELSE transactions_count END AS transactions_count
 FROM Visits AS a LEFT JOIN Transaction_cnt AS b ON a.visit_date = b.transaction_date AND a.user_id = b.user_id) AS t RIGHT JOIN rn 
ON rn.k = t.transactions_count
WHERE k<=(SELECT MAX(transactions_count) FROM Transaction_cnt)
GROUP BY k
--ORDER BY transactions_count

---------------------- 1384 -------------------------

CREATE TABLE UserActivity
    ([user_name] varchar(5), [activity] varchar(7), [start_date] datetime, [end_date] datetime)
;
    
INSERT INTO UserActivity
    ([user_name], [activity], [start_date], [end_date])
VALUES
    ('Alice', 'Travel', '2020-02-12 00:00:00', '2020-02-20 00:00:00'),
    ('Alice', 'Dancing', '2020-02-21 00:00:00', '2020-02-23 00:00:00'),
    ('Alice', 'Travel', '2020-02-24 00:00:00', '2020-02-28 00:00:00'),
    ('Bob', 'Travel', '2020-02-11 00:00:00', '2020-02-18 00:00:00')
;

/* Write an SQL query to show the second most recent activity of each user.*/

/*
WITH t1 AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY end_date DESC) AS dateRank
FROM UserActivity),
t2 AS 
(SELECT user_name
 FROM UserActivity
 GROUP BY user_name
 HAVING COUNT(*) =1)

SELECT user_name, activity, start_date, end_date
FROM UserActivity
WHERE user_name IN (SELECT user_name FROM t2)
UNION ALL
SELECT user_name, activity, start_date, end_date
FROM t1
WHERE dateRank=2
*/

------------------1384 -------------

DROp TABLE Product;
CREATE TABLE Product
    ([product_id] int, [product_name] varchar(11))
;
    
INSERT INTO Product
    ([product_id], [product_name])
VALUES
    (1, 'LC Phone'),
    (2, 'LC T-Shirt'),
    (3, 'LC Keychain')
;

DROP TABLE Sales;
CREATE TABLE Sales
    ([product_id] int, [period_start] datetime, [period_end] datetime, [average_daily_sales] int)
;
    
INSERT INTO Sales
    ([product_id], [period_start], [period_end], [average_daily_sales])
VALUES
    (1, '2019-01-25 00:00:00', '2019-02-28 00:00:00', 100),
    (2, '2018-12-01 00:00:00', '2020-01-01 00:00:00', 10),
    (3, '2019-12-01 00:00:00', '2020-01-31 00:00:00', 1)
;

/* --Write an SQL query to report the Total sales amount of each item for each year, 
 with corresponding product name, product_id, product_name and report_year.*/

/*
WITH salesDays AS
(SELECT product_id,
 2018 AS report_year,
 CASE WHEN period_start>='2019-01-01' OR period_end<='2018-01-01' THEN 0
      WHEN period_start<='2018-12-31' AND period_start>= '2018-01-01' AND period_end<='2018-12-31' THEN DATEDIFF(day, period_start, period_end) +1
      WHEN period_start<='2018-12-31' AND period_start>= '2018-01-01' AND period_end>'2018-12-31' THEN DATEDIFF(day, period_start, '2018-12-31')+1
      ELSE DATEDIFF(day, '2018-01-01', period_end)+1 END AS sales_days,
 average_daily_sales
 FROM Sales
 UNION
SELECT product_id,
 2019 AS report_year,
 CASE WHEN period_start>='2020-01-01' OR period_end<='2019-01-01' THEN 0
      WHEN period_start<='2019-12-31' AND period_start>= '2019-01-01' AND period_end<='2019-12-31' THEN DATEDIFF(day, period_start, period_end) +1
      WHEN period_start<='2019-12-31' AND period_start>= '2019-01-01' AND period_end>'2019-12-31' THEN DATEDIFF(day, period_start, '2019-12-31')+1
      ELSE DATEDIFF(day, '2019-01-01', period_end)+1 END AS sales_days,
 average_daily_sales
FROM Sales
 UNION
SELECT product_id,
 2020 AS report_year,
 CASE WHEN period_start>='2021-01-01' OR period_end<='2020-01-01' THEN 0
      WHEN period_start<='2020-12-31' AND period_start>= '2020-01-01' AND period_end<='2020-12-31' THEN DATEDIFF(day, period_start, period_end) +1
      WHEN period_start<='2020-12-31' AND period_start>= '2020-01-01' AND period_end>'2020-12-31' THEN DATEDIFF(day, period_start, '2020-12-31')+1
      ELSE DATEDIFF(day, '2020-01-01', period_end)+1 END AS sales_days,
 average_daily_sales
FROM Sales)

SELECT a.product_id, product_name, report_year, sales_days, sales_days*average_daily_sales AS tot_sales
FROM SalesDays AS a INNER JOIN Product AS b ON a.product_id = b.product_id
--WHERE sales_days>0;

SELECT DATEDIFF(day, '2020-01-01', '2020-01-01 00:00:00')+1

--------------------- 1412 ----------------
/*
DROP TABLE Student;
CREATE TABLE Student
    ([student_id] int, [student_name] varchar(8))
;
    
INSERT INTO Student
    ([student_id], [student_name])
VALUES
    (1, 'Daniel'),
    (2, 'Jade'),
    (3, 'Stella'),
    (4, 'Jonathan'),
    (5, 'Will')
;

CREATE TABLE Exam
    ([exam_id] int, [student_id] int, [score] int)
;
    
INSERT INTO Exam
    ([exam_id], [student_id], [score])
VALUES
    (10, 1, 70),
    (10, 2, 80),
    (10, 3, 90),
    (20, 1, 80),
    (30, 1, 70),
    (30, 3, 80),
    (30, 4, 90),
    (40, 1, 60),
    (40, 2, 70),
    (40, 4, 80)
;
-- A "quite" student is the one who took at least one exam and didn't score neither the high score nor the low score.
--Write an SQL query to report the students (student_id, student_name) being "quiet" in ALL exams.
-- Don't return the student who has never taken any exam. Return the result table ordered by student_id.
WITH t AS (
    SELECT student_id
    FROM Student 
    WHERE student_id NOT IN (SELECT student_id FROM Exam GROUP BY student_id)),
    t1 AS (
        SELECT *, RANK() OVER(PARTITION BY exam_id ORDER BY score) AS asc_rank                             
        FROM Exam) ,
    t2 AS (
        SELECT *, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS desc_rank
        FROM Exam)
        
SELECT student_id, student_name
FROM Student
WHERE student_id NOT IN (SELECT student_id FROM t1 WHERE asc_rank = 1)
AND student_id NOT IN (SELECT student_id FROM t)
AND student_id NOT IN (SELECT student_id from t2 WHERE desc_rank=1)
*/

----------------- 1479 ------------------

/*
DROP TABLE Orders;
CREATE TABLE Orders
    ([order_id] int, [customer_id] int, [order_date] datetime, [item_id] int, [quantity] int)
;
    
INSERT INTO Orders
    ([order_id], [customer_id], [order_date], [item_id], [quantity])
VALUES
    (1, 1, '2020-06-01 00:00:00', 1, 10),
    (2, 1, '2020-06-08 00:00:00', 2, 10),
    (3, 2, '2020-06-02 00:00:00', 1, 5),
    (4, 3, '2020-06-03 00:00:00', 3, 5),
    (5, 4, '2020-06-04 00:00:00', 4, 1),
    (6, 4, '2020-06-05 00:00:00', 5, 5),
    (7, 5, '2020-06-05 00:00:00', 1, 10),
    (8, 5, '2020-06-14 00:00:00', 4, 5),
    (9, 5, '2020-06-21 00:00:00', 3, 5)
;

CREATE TABLE Items
    ([item_id] int, [item_name] varchar(14), [item_category] varchar(7))
;
    
INSERT INTO Items
    ([item_id], [item_name], [item_category])
VALUES
    (1, 'LC Alg. Book', 'Book'),
    (2, 'LC DB. Book', 'Book'),
    (3, 'LC SmarthPhone', 'Phone'),
    (4, 'LC Phone 2020', 'Phone'),
    (5, 'LC SmartGlass', 'Glasses'),
    (6, 'LC T-Shirt XL', 'T-Shirt')
;

-- Write an SQL query to report how many units in each category have been ordered on each day of the week.
-- Return the result table ordered by category.

-- +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
-- | Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
-- +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
-- | Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
-- | Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
-- | Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
-- | T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
-- +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
WITH mon AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Monday 
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=2) AS b 
    ON a.item_id = b.item_id
        GROUP BY item_category),
    tue AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Tuesday
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=3) AS b 
    ON a.item_id = b.item_id
    GROUP BY item_category),
    wed AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Wednesday
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=4) AS b 
    ON a.item_id = b.item_id
        GROUP BY item_category),
    thurs AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Thursday
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=5) AS b 
    ON a.item_id = b.item_id
        GROUP BY item_category), 
    fri AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Friday
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=6) AS b 
    ON a.item_id = b.item_id
        GROUP BY item_category),

sat AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Saturday
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=7) AS b 
    ON a.item_id = b.item_id
    GROUP BY item_category),

sun AS (
    SELECT item_category, SUM(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS Sunday
    FROM Items AS a LEFT JOIN 
    (SELECT * 
     FROM Orders AS b 
     WHERE DATEPART(weekday, order_date)=1) AS b 
    ON a.item_id = b.item_id
    GROUP BY item_category)

SELECT mon.item_category, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
 FROM mon INNER JOIN tue ON mon.item_category = tue.item_category
 INNER JOIN wed ON mon.item_category =wed.item_category
 INNER JOIN thurs on mon.item_category = thurs.item_category
 INNER JOIN fri on mon.item_category = fri.item_category
 INNER JOIN sat ON mon.item_category = sat.item_category
 INNER JOIN sun ON mon.item_category = sun.item_category
*/
--------------------- 1635 -------------------------

CREATE TABLE Drivers
    ([driver_id] int, [join_date] datetime)
;
    
INSERT INTO Drivers
    ([driver_id], [join_date])
VALUES
    (10, '2019-12-10 00:00:00'),
    (8, '2020-01-03 00:00:00'),
    (5, '2020-02-16 00:00:00'),
    (7, '2020-03-08 00:00:00'),
    (4, '2020-05-17 00:00:00'),
    (1, '2020-10-24 00:00:00'),
    (6, '2021-01-05 00:00:00')
;

CREATE TABLE Rides
    ([ride_id] int, [user_id] varchar(8), [requested_at] datetime)
;
    
INSERT INTO Rides
    ([ride_id], [user_id], [requested_at])
VALUES
    (6, '75', '2019-12-09 00:00:00'),
    (1, '54', '2020-02-09 00:00:00'),
    (10, '3/3/1900', '2020-03-04 00:00:00'),
    (19, '2/8/1900', '2020-04-06 00:00:00'),
    (3, '41', '2020-06-03 00:00:00'),
    (13, '52', '2020-06-22 00:00:00'),
    (7, '69', '2020-07-16 00:00:00'),
    (17, '70', '2020-08-25 00:00:00'),
    (20, '81', '2020-11-02 00:00:00'),
    (5, '57', '2020-11-09 00:00:00'),
    (2, '42', '2020-12-09 00:00:00'),
    (11, '68', '2021-01-11 00:00:00'),
    (15, '32', '2021-01-17 00:00:00'),
    (12, '11', '2021-01-19 00:00:00'),
    (14, '18', '2021-01-27 00:00:00')
;

CREATE TABLE AcceptedRides
    ([ride_id] int, [driver_id] int, [ride_distance] int, [ride_duration] int)
;
    
INSERT INTO AcceptedRides
    ([ride_id], [driver_id], [ride_distance], [ride_duration])
VALUES
    (10, 10, 63, 38),
    (13, 10, 73, 96),
    (7, 8, 100, 28),
    (17, 7, 119, 68),
    (20, 1, 121, 92),
    (5, 7, 42, 101),
    (2, 4, 6, 38),
    (11, 8, 37, 43),
    (15, 8, 108, 82),
    (12, 8, 38, 34),
    (14, 1, 90, 74)
;

-- Write an SQL query to report the following statistics for each month of 2020:

-- The number of drivers currently with the Hopper company by the end of the month (active_drivers).

/*
WITH t0 AS (
    SELECT '2020-01-01' AS month 
    UNION ALL
    SELECT '2020-02-01' AS month 
    UNION ALL
    SELECT '2020-03-01' AS month 
    UNION ALL
    SELECT '2020-04-01' AS month 
    UNION ALL
    SELECT '2020-05-01' AS month 
    UNION ALL
    SELECT '2020-06-01' AS month 
    UNION ALL
    SELECT '2020-07-01' AS month 
    UNION ALL
    SELECT '2020-08-01' AS month 
    UNION ALL
    SELECT '2020-09-01' AS month 
    UNION ALL
    SELECT '2020-10-01' AS month 
    UNION ALL
    SELECT '2020-11-01' AS month 
    UNION ALL
    SELECT '2020-12-01' AS month ),
    t AS (
       SELECT month AS month_start, 
              DATEADD(month, 1, DATEADD(day,-1,month)) AS month_end
        FROM t0),
    t1 AS (
       SELECT MONTH(month_start) AS mon_num, COUNT(DISTINCT driver_id) AS driver_cnt
       FROM Drivers CROSS JOIN t
       WHERE join_date <= month_end
       GROUP BY MONTH(month_start)),
    t2 AS (
        SELECT a.ride_id, MONTH(requested_at) As mon_num
        FROM AcceptedRides AS a INNER JOIN Rides AS b ON a.ride_id = b.ride_id
        CROSS JOIN t
        WHERE requested_at>=month_start AND requested_at<=month_end)

SELECT tt.mon_num, 
        CASE WHEN driver_cnt IS NULL THEN 0 ELSE driver_cnt END AS driver_cnt,
        SUM(CASE WHEN ride_id IS NULL THEN 0 ELSE 1 END) AS acceptedRide_cnt
 FROM (SELECT MONTH(month_start) AS mon_num FROM t) AS tt 
        LEFT JOIN t1 ON tt.mon_num =t1.mon_num
        LEFT JOIN t2 ON tt.mon_num = t2.mon_num
 GROUP BY tt.mon_num, driver_cnt

-- The number of accepted rides in that month (accepted_rides).
--Return the result table ordered by month in ascending order, where month is the month's number (January is 1, February is 2, etc.).

*/
--------------------- 1645 ---------------------------
-- Write an SQL query to report the percentage of working drivers (working_percentage) for each month of 2020 where:
-- percentage_month = #drivers who accepted at least one rides during the month/ #available drivers during the month
-- Note that if the number of available drivers during a month is zero, we consider the working_percentage to be 0.

-- Return the result table ordered by month in ascending order, where month is the month's number (January is 1, February is 2, etc.). 
-- Round working_percentage to the nearest 2 decimal places.
/*
WITH t0 AS (
    SELECT '2020-01-01' AS month 
    UNION ALL
    SELECT '2020-02-01' AS month 
    UNION ALL
    SELECT '2020-03-01' AS month 
    UNION ALL
    SELECT '2020-04-01' AS month 
    UNION ALL
    SELECT '2020-05-01' AS month 
    UNION ALL
    SELECT '2020-06-01' AS month 
    UNION ALL
    SELECT '2020-07-01' AS month 
    UNION ALL
    SELECT '2020-08-01' AS month 
    UNION ALL
    SELECT '2020-09-01' AS month 
    UNION ALL
    SELECT '2020-10-01' AS month 
    UNION ALL
    SELECT '2020-11-01' AS month 
    UNION ALL
    SELECT '2020-12-01' AS month ),
    t AS (
       SELECT month AS month_start, 
              DATEADD(month, 1, DATEADD(day,-1,month)) AS month_end
        FROM t0),
    t1 AS (
       SELECT MONTH(month_start) AS mon_num, COUNT(DISTINCT driver_id) AS driver_cnt
       FROM Drivers CROSS JOIN t
       WHERE join_date <= month_end
       GROUP BY MONTH(month_start)),
    t2 AS (
        SELECT a.ride_id, driver_id, MONTH(requested_at) As mon_num
        FROM AcceptedRides AS a INNER JOIN Rides AS b ON a.ride_id = b.ride_id
        CROSS JOIN t
        WHERE requested_at>=month_start AND requested_at<=month_end)
 
 SELECT tt.mon_num,
 driver_cnt,
 working_driver,
        CASE WHEN driver_cnt=0 OR working_driver IS NULL THEN 0.00 ELSE CAST(working_driver*1.00/driver_cnt*1.00 AS decimal(4,2)) END AS working_percentage
 FROM 
 (SELECT MONTH(month_start) AS mon_num 
  FROM t) AS tt LEFT JOIN t1 ON tt.mon_num = t1.mon_num
LEFT JOIN
(SELECT mon_num,
 COUNT(DISTINCT driver_id) AS working_driver
 FROM t2
 GROUP BY mon_num) AS tt2 on tt.mon_num = tt2.mon_num
 */
--------------------- 1651 ------------------------------
-- Write an SQL query to compute the average_ride_distance and average_ride_duration of every 3-month window starting from January - March 2020 to October - December 2020. Round average_ride_distance and average_ride_duration to the nearest two decimal places.

-- The average_ride_distance is calculated by summing up the total ride_distance values from the three months and dividing it by 3. 
-- The average_ride_duration is calculated in a similar way.
-- Return the result table ordered by month in ascending order, where month is the starting month's number (January is 1, February is 2, etc.).

/*
WITH t0 AS (
    SELECT '2020-01-01' AS month 
    UNION ALL
    SELECT '2020-02-01' AS month 
    UNION ALL
    SELECT '2020-03-01' AS month 
    UNION ALL
    SELECT '2020-04-01' AS month 
    UNION ALL
    SELECT '2020-05-01' AS month 
    UNION ALL
    SELECT '2020-06-01' AS month 
    UNION ALL
    SELECT '2020-07-01' AS month 
    UNION ALL
    SELECT '2020-08-01' AS month 
    UNION ALL
    SELECT '2020-09-01' AS month 
    UNION ALL
    SELECT '2020-10-01' AS month 
    UNION ALL
    SELECT '2020-11-01' AS month 
    UNION ALL
    SELECT '2020-12-01' AS month ),
    t AS (
       SELECT month AS month_start, 
              DATEADD(month, 1, DATEADD(day,-1,month)) AS month_end,
        MONTH(month) AS mon_num
        FROM t0),
    t2 AS (
        SELECT MONTH(requested_at) As mon_num,
        SUM(ride_distance) AS monthly_ride_distance, 
        SUM(ride_duration) AS monthly_ride_duration
        FROM AcceptedRides AS a INNER JOIN Rides AS b ON a.ride_id = b.ride_id
        CROSS JOIN t
        WHERE requested_at>=month_start AND requested_at<=month_end
        GROUP BY MONTH(requested_at)),
t3 AS (SELECT tt0.mon_num AS mon1, tt1.mon_num AS mon2
       FROM t AS tt0, t AS tt1
       WHERE (tt1.mon_num = tt0.mon_num+2 OR tt1.mon_num=tt0.mon_num OR tt1.mon_num = tt0.mon_num+1) AND tt0.mon_num<=10
       )

SELECT t3.mon1,       
       SUM(monthly_ride_distance)/COUNT(*) AS avg_monthly_ride_distance,
       SUM(monthly_ride_duration)/COUNT(*) AS avg_monthly_ride_duration
FROM t3 LEFT JOIN t2 ON t3.mon2 = t2.mon_num
GROUP BY t3.mon1

*/
------------------- 1767 ----------------------

CREATE TABLE Tasks
    ([task_id] int, [subtasks_count] int)
;
    
INSERT INTO Tasks
    ([task_id], [subtasks_count])
VALUES
    (1, 3),
    (2, 2),
    (3, 4)
;

CREATE TABLE Executed
    ([task_id] int, [subtask_id] int)
;
    
INSERT INTO Executed
    ([task_id], [subtask_id])
VALUES
    (1, 2),
    (3, 1),
    (3, 2),
    (3, 3),
    (3, 4)
;

-- Write an SQL query to report the IDs of the missing subtasks for each task_id.

-- Return the result table in any order.
/*
WITH t AS (
 SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n AS k
 FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
      (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
      (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
      (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
 ),
 
*/
----AAAAAAAAAAAAAA---------SOl1 using EXISTS ----------AAAAAAAAAAAAA-----------

/*
WITH ones AS (
    SELECT 0 AS k UNION 
    SELECT 1 AS k UNION 
    SELECT 2 AS k UNION
    SELECT 3 AS k UNION
    SELECT 4 AS k UNION 
    SELECT 5 AS k UNION
    SELECT 6 AS k UNION
    SELECT 7 AS k UNION
    SELECT 8 AS k UNION
    SELECT 9 AS k),
    t AS (
        SELECT ones.k+tens.k*10 AS k
        FROM ones AS ones, ones AS tens--, ones AS hundreds
        ),
   
     t1 AS (
    SELECT task_id, k
    FROM Tasks CROSS JOIN t
    WHERE k<=subtasks_count AND k>0)

SELECT *
FROM t1
--ORDER BY k
WHERE NOT EXISTS (SELECT * FROM Executed b WHERE t1.task_id = b.task_id AND t1.k = b.subtask_id);

----AAAAAAAAAAAAAA---------SOl2 using correlated query ----------AAAAAAAAAAAAA-----------

WITH ones AS (
    SELECT 0 AS k UNION 
    SELECT 1 AS k UNION 
    SELECT 2 AS k UNION
    SELECT 3 AS k UNION
    SELECT 4 AS k UNION 
    SELECT 5 AS k UNION
    SELECT 6 AS k UNION
    SELECT 7 AS k UNION
    SELECT 8 AS k UNION
    SELECT 9 AS k),
    t AS (
        SELECT ones.k+tens.k*10 AS k
        FROM ones AS ones, ones AS tens--, ones AS hundreds
        ),
   
     t1 AS (
    SELECT task_id, k
    FROM Tasks CROSS JOIN t
    WHERE k<=subtasks_count AND k>0)

SELECT t1.task_id, k
FROM t1, Executed AS e
WHERE (t1.task_id = e.task_id AND t1.k NOT IN (SELECT e2.subtask_id FROM Executed e2 WHERE e2.task_id = t1.task_id)) OR t1.task_id NOT IN (SELECT task_id FROM Executed)
GROUP BY t1.task_id, k;

----AAAAAAAAAAAAAA---------SOl1 using LEFT JOIN ----------AAAAAAAAAAAAA-----------

SELECT t1.task_id, k
FROM t1 LEFT OUTER JOIN Executed AS e
ON t1.task_id = e.task_id AND t1.k = e.subtask_id
WHERE e.subtask_id IS NULL
GROUP BY t1.task_id, k

*/
    -------------------------- 1112 -----------------------------
/*
CREATE TABLE Enrollments
    ([student_id] int, [course_id] int, [grade] int)
;
    
INSERT INTO Enrollments
    ([student_id], [course_id], [grade])
VALUES
    (2, 2, 95),
    (2, 3, 95),
    (1, 1, 90),
    (1, 2, 99),
    (3, 1, 80),
    (3, 2, 75),
    (3, 3, 82)
;
*/
/*Write a SQL query to find the highest grade with its corresponding course for each student. 
In case of a tie, you should find the course with the smallest course_id. The output must be sorted by increasing student_id.*/
/*
WITH t AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY grade DESC, course_id) AS student_rank
FROM Enrollments)
SELECT student_id, course_id, grade
FROM t
WHERE student_rank=1;
*/

--------------  1126 ------------------
/*
CREATE TABLE Events
    ([business_id] int, [event_type] varchar(10), [occurances] int)
;
    
INSERT INTO Events
    ([business_id], [event_type], [occurances])
VALUES
    (1, 'reviews', 7),
    (3, 'reviews', 3),
    (1, 'ads', 11),
    (2, 'ads', 7),
    (3, 'ads', 6),
    (1, 'page views', 3),
    (2, 'page views', 12)
;
*/
/* Write an SQL query to find all active businesses.
An active business is a business that has more than one event type with occurences greater than the average occurences of that event type among all businesses.*/
/*
WITH t1 AS (
    SELECT event_type, AVG(occurances) AS occurances
    FROM Events
    GROUP BY event_type)
 SELECT a.business_id
 FROM Events AS a INNER JOIN t1 As b ON a.event_type = b.event_type
 WHERE a.occurances >b.occurances
 GROUP BY a.business_id
 HAVING COUNT(DISTINCT a.event_type)>1
*/    
--------------- 1132 ------------------

/*
CREATE TABLE Actions
    ([userid] int, [post_id] int, [action_date] datetime, [action] varchar(6), [extra] varchar(6))
;
    
INSERT INTO Actions
    ([userid], [post_id], [action_date], [action], [extra])
VALUES
    (1, 1, '2019-07-01 00:00:00', 'view', NULL),
    (1, 1, '2019-07-01 00:00:00', 'like', NULL),
    (1, 1, '2019-07-01 00:00:00', 'share', NULL),
    (2, 2, '2019-07-04 00:00:00', 'view', NULL),
    (2, 2, '2019-07-04 00:00:00', 'report', 'spam'),
    (3, 4, '2019-07-04 00:00:00', 'view', NULL),
    (3, 4, '2019-07-04 00:00:00', 'report', 'spam'),
    (4, 3, '2019-07-02 00:00:00', 'view', NULL),
    (4, 3, '2019-07-02 00:00:00', 'report', 'spam'),
    (5, 2, '2019-07-03 00:00:00', 'view', NULL),
    (5, 2, '2019-07-03 00:00:00', 'report', 'racism'),
    (5, 5, '2019-07-03 00:00:00', 'view', NULL),
    (5, 5, '2019-07-03 00:00:00', 'report', 'racism')
;

CREATE TABLE Removals
    ([post_id] int, [remove_date] datetime)
;
    
INSERT INTO Removals
    ([post_id], [remove_date])
VALUES
    (2, '2019-07-20 00:00:00'),
    (3, '2019-07-18 00:00:00')
;

--Write a piece of SQL to find out: Among the posts reported as spam, the daily average percentage of removed posts, rounded to 2 decimal places.
SELECT CAST(AVG(SpamRemovalRate) AS decimal(4,2)) AS RemovalRate
FROM(
SELECT action_date, SUM(CASE WHEN a.extra = 'spam' AND b.post_id IS NOT NULL THEN 1.00 ELSE 0 END)/SUM(CASE WHEN a.extra = 'spam' THEN 1 ELSE 0 END) AS SpamRemovalRate
FROM Actions AS a LEFT JOIN Removals AS b ON a.post_id = b.post_id
WHERE extra = 'spam'
GROUP BY action_date) AS t

---------------- 1149 ------------

/*
CREATE TABLE Views
    ([article_id] int, [author_id] int, [viewer_id] int, [view_date] datetime)
;
    
INSERT INTO Views
    ([article_id], [author_id], [viewer_id], [view_date])
VALUES
    (1, 3, 5, '2019-08-01 00:00:00'),
    (3, 4, 5, '2019-08-01 00:00:00'),
    (1, 3, 6, '2019-08-02 00:00:00'),
    (2, 7, 7, '2019-08-01 00:00:00'),
    (2, 7, 6, '2019-08-02 00:00:00'),
    (4, 7, 1, '2019-07-22 00:00:00'),
    (3, 4, 4, '2019-07-21 00:00:00'),
    (3, 4, 4, '2019-07-21 00:00:00')
;

--编写一条 SQL 查询来找出在同一天阅读至少两篇文章的人，结果按照 id 升序排序。
SELECT viewer_id
FROM Views
GROUP BY viewer_id,view_date
HAVING COUNT(DISTINCT article_id) >1
ORDER BY viewer_id
*/
------------------ 1158 -----------------

/*
CREATE TABLE Users
    ([user_id] int, [join_date] datetime, [favorite_brand] varchar(7))
;
    
INSERT INTO Users
    ([user_id], [join_date], [favorite_brand])
VALUES
    (1, '2018-01-01 00:00:00', 'lenovo'),
    (2, '2018-02-09 00:00:00', 'samsung'),
    (3, '2018-01-19 00:00:00', 'lg'),
    (4, '2018-05-21 00:00:00', 'hp')
;

DROP TABLE Orders;
CREATE TABLE Orders
    ([order_id] int, [order_date] datetime, [item_id] int, [buyer_id] int, [seller_id] int)
;
    
INSERT INTO Orders
    ([order_id], [order_date], [item_id], [buyer_id], [seller_id])
VALUES
    (1, '2019-08-01 00:00:00', 4, 1, 2),
    (2, '2018-08-02 00:00:00', 2, 1, 3),
    (3, '2019-08-03 00:00:00', 3, 2, 3),
    (4, '2018-08-04 00:00:00', 1, 4, 2),
    (5, '2018-08-04 00:00:00', 1, 3, 4),
    (6, '2019-08-05 00:00:00', 2, 2, 4)
;

CREATE TABLE Items
    ([item_id] int, [item_brand] varchar(7))
;
    
INSERT INTO Items
    ([item_id], [item_brand])
VALUES
    (1, 'samsung'),
    (2, 'lenovo'),
    (3, 'lg'),
    (4, 'hp')
;
*/
-- Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.
--sol1
/*
WITH t AS (
    SELECT buyer_id, COUNT(order_id) AS orders
    FROM Orders
    WHERE YEAR(order_date) = 2019
    GROUP BY buyer_id)
SELECT user_id, join_date, CASE WHEN orders IS NULL THEN 0 ELSE orders END AS orders2019
FROM Users AS a LEFT JOIN t AS b ON a.user_id = b.buyer_id
*/
--sol2
/*
SELECT a.user_id, join_date, SUM(CASE WHEN b.buyer_id IS NOT NULL AND YEAR(order_date) = 2019 THEN 1 ELSE 0 END) AS orders
FROM Users AS a LEFT JOIN Orders AS b ON a.user_id = b.buyer_id
GROUP BY a.user_id, join_date;
*/

------------------- 1164 -------------------

/*
CREATE TABLE Products
    ([product_id] int, [new_price] int, [change_date] datetime)
;
    
INSERT INTO Products
    ([product_id], [new_price], [change_date])
VALUES
    (1, 20, '2019-08-14 00:00:00'),
    (2, 50, '2019-08-14 00:00:00'),
    (1, 30, '2019-08-15 00:00:00'),
    (1, 35, '2019-08-16 00:00:00'),
    (2, 65, '2019-08-17 00:00:00'),
    (3, 20, '2019-08-18 00:00:00')
;

-- Write an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.
WITH t AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY change_date DESC) AS product_rank
    FROM Products
    WHERE change_date<='2019-08-16'),
    t2 AS(
        SELECT product_id
        FROM Products
        GROUp BY product_id)
        
  SELECT t2.product_id, CASE WHEN t.product_id IS NULL THEN 10 ELSE new_price END AS price
  FROM t2 LEFT JOIN (SELECT * FROM t WHERE product_rank=1) AS t 
  ON t2.product_id = t.product_id
*/

------------------ 1193 --------------

/*
DROP TABLE Transactions;
CREATE TABLE Transactions
    ([id] int, [country] varchar(2), [state] varchar(8), [amount] int, [trans_date] datetime)
;
    
INSERT INTO Transactions
    ([id], [country], [state], [amount], [trans_date])
VALUES
    (121, 'US', 'approved', 1000, '2018-12-18 00:00:00'),
    (122, 'US', 'declined', 2000, '2018-12-10 00:00:00'),
    (123, 'US', 'approved', 2000, '2019-01-01 00:00:00'),
    (124, 'DE', 'approved', 2000, '2019-01-07 00:00:00')
;

-- Write an SQL query to find for each month and country, 
-- the number of transactions and their total amount, the number of approved transactions and their total amount.
WITH tot_trans AS (
    SELECT country, EOMONTH(Trans_date) AS month, COUNT(id) AS trans_cnt, SUM(amount) AS trans_amount
    FROM Transactions d 
    GROUP BY country, EOMONTH(trans_date)),
    approved_trans AS (
    SELECT country, EOMONTH(Trans_date) AS month, COUNT(id) AS trans_cnt, SUM(amount) AS trans_amount
    FROM Transactions d 
        WHERE
        state = 'approved'
    GROUP BY country, EOMONTH(trans_date))

SELECT a.month, 
        a.country,
        a.trans_cnt AS tot_trans_cnt,
        a.trans_amount AS tot_trans_amount,
        CASE WHEN b.trans_cnt IS NULL THEN 0 ELSE b.trans_cnt END AS approved_trans_cnt,
        CASE WHEN b.trans_amount IS NULL THEN 0 ELSE b.trans_amount END AS approved_trans_amount
FROM tot_trans AS a LEFT JOIN approved_trans AS b ON a.month = b.month AND a. country = b.country;

*/
---------------- 1205 -----------------

DROP TABLE Transactions;

CREATE TABLE Transactions
    ([id] int, [country] varchar(2), [state] varchar(8), [amount] int, [trans_date] datetime)
;
    
INSERT INTO Transactions
    ([id], [country], [state], [amount], [trans_date])
VALUES
    (101, 'US', 'approved', 1000, '2019-05-18 00:00:00'),
    (102, 'US', 'declined', 2000, '2019-05-19 00:00:00'),
    (103, 'US', 'approved', 3000, '2019-06-10 00:00:00'),
    (104, 'US', 'approved', 4000, '2019-06-13 00:00:00'),
    (105, 'US', 'approved', 5000, '2019-06-15 00:00:00')
;

CREATE TABLE Chargeback
    ([trans_id] int, [trans_date] datetime)
;
    
INSERT INTO Chargeback
    ([trans_id], [trans_date])
VALUES
    (102, '2019-05-29 00:00:00'),
    (101, '2019-06-30 00:00:00'),
    (105, '2019-09-18 00:00:00')
;

-- Write an SQL query to find for each month and country, the number of approved transactions and their total amount, 
-- the number of chargebacks and their total amount.
WITH t0 AS (
    SELECT country, EOMONTH(trans_date) AS month
    FROM Transactions
    GROUP BY country, EOMONTH(trans_date)
    UNION
    SELECT country, EOMONTH(a.trans_date) AS month
    FROM Chargeback AS a INNER JOIN Transactions AS b ON a.trans_id = b.id
    GROUP BY country, EOMONTH(a.trans_date)),
    t1 AS (
        SELECT country, EOMONTH(trans_date) AS month, COUNT(id) AS trans_cnt, SUM(amount) AS trans_amount
        FROM Transactions 
        WHERE state = 'approved'
        GROUP BY country, EOMONTH(trans_date)),
    t2 AS (
        SELECT country, EOMONTH(a.trans_date) AS month, COUNT(id) AS trans_cnt, SUM(amount) AS chargeback_amount
        FROM Chargeback AS a INNER JOIN Transactions AS b ON a.trans_id = b.id
        GROUP BY country, EOMONTH(a.trans_date))
        
 SELECT t0.country, t0.month, 
        CASE WHEN t1.trans_cnt IS NULL THEN 0 ELSE t1.trans_cnt END AS approved_cnt, 
        CASE WHEN t1.trans_amount IS NULL THEN 0 ELSE t1.trans_amount END AS approved_amount, 
        CASE WHEN t2.trans_cnt IS NULL THEN 0 ELSE t2.trans_cnt END AS chargeback_cnt, 
        CASE WHEN t2.chargeback_amount IS NULL THEN 0 ELSE t2.chargeback_amount END AS hargeback_amount
 FROM t0 LEFT JOIN t1 ON t0.country = t1.country AND t0.month = t1.month
 LEFT JOIN t2 ON t0.country = t2.country AND t0.month = t2.month;
 
 
 ------- sol2 --------
 
 WITH t1 AS (
        SELECT country, EOMONTH(trans_date) AS month, COUNT(id) AS trans_cnt, SUM(amount) AS trans_amount
        FROM Transactions 
        WHERE state = 'approved'
        GROUP BY country, EOMONTH(trans_date)),
      t2 AS (
        SELECT country, EOMONTH(a.trans_date) AS month, COUNT(id) AS trans_cnt, SUM(amount) AS chargeback_amount
        FROM Chargeback AS a INNER JOIN Transactions AS b ON a.trans_id = b.id
        GROUP BY country, EOMONTH(a.trans_date))

SELECT CASE WHEN t1.country IS NULL THEN t2.country ELSE t1.country END AS country,
       CASE WHEN t1.month IS NULL THEN t2.month ELSE t1.month END AS month,
       CASE WHEN t1.trans_cnt IS NULL THEN 0 ELSE t1.trans_cnt END AS approved_cnt, 
        CASE WHEN t1.trans_amount IS NULL THEN 0 ELSE t1.trans_amount END AS approved_amount, 
        CASE WHEN t2.trans_cnt IS NULL THEN 0 ELSE t2.trans_cnt END AS chargeback_cnt, 
        CASE WHEN t2.chargeback_amount IS NULL THEN 0 ELSE t2.chargeback_amount END AS hargeback_amount
FROM t1 FULL OUTER JOIN t2 ON t1.country = t2.country AND t1.month = t2.month;

View schema
Execution time: 0,38 sec, rows selected: 24, rows affected: 266, absolute service time: 0,56 sec, absolute service time: 0,6 sec