create table department (
	id int,
	revenue int,
	month varchar(255)
);

insert into department values
(1, 8000, 'Jan'),
(2, 9000, 'Jan'),
(3, 10000, 'Feb'),
(1, 7000, 'Feb'),
(1, 6000, 'Mar');

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

create table employee (
	id int primary key auto_increment,
	salary int
);

insert into employee (salary) values(100),(200),(300);

select e.salary from (
	select
		salary,
		case when salary is not null then @rank:= @rank + 1 end as 排名
	from (select @rank:=0) as init, (
		select salary from employee group by salary order by salary desc
	) as t
) as e where e.排名 = 2;

select salary from employee group by salary order by salary desc limit 1, 1;

create table seat (
	id int,
	student varchar(255)
);

insert into seat values
(1, 'Doris'),
(2, 'Abbot'),
(3, 'Green'),
(4, 'Emerson'),
(5, 'Jeames');

select 
	(
		case
			when mod(id, 2) != 0 and counts != id then id + 1
			when mod(id, 2) != 0 and counts = id then id
			else id - 1
		end
	) as id,
	student
from seat,
(select count(*) as counts from seat) as count
order by id asc;

select s.id, 
	if(mod(s.id, 2) = 1, s.back, s.ahead) as student 
from (
	select 
		id, student,
		lag(student, 1, student) over() as ahead,
		lead(student, 1, student) over() as back
	from seat
) as s;

create table employee(
  id int,
  month int,
  salary int
)

insert into employee values
(1,1,20),
(2,1,20),
(1,2,30),
(2,2,30),
(3,2,40),
(1,3,40),
(3,3,60),
(1,4,60),
(3,4,70),
(1,7,90),
(1,8,90);

select *, sum(column) over(partition by id order by age) from table_name;

create table activity (
	player_id int,
	device_id int,
	event_date date,
	games_played int
);

insert into activity values
(1, 2, '2016-03-01', 5),
(1, 2, '2016-05-02', 6),
(1, 3, '2017-06-25', 1),
(3, 1, '2016-03-02', 0),
(3, 4, '2018-07-03', 5);

select player_id, event_date,
	sum(
		case
			when @pre_player_id = player_id then @n:=@n+games_played
			when @pre_player_id:= player_id then @n:=games_played
		end
	) as games_played_so_far
from (
	select * from activity order by player_id, event_date
) temp, (select @pre_player_id:=null, @n:=0) init
group by player_id, event_date;

select
	player_id,
	event_date,
	sum(games_played)
	 over(partition by player_id order by event_date) as games_played_so_far
from activity;

select a.player_id, a.event_date, sum(b.games_played) as games_played_so_far
from activity a join activity b
on a.player_id = b.player_id where a.event_date >= b.event_date
group by a.player_id, a.event_date;

create table activity (
	player_id int,
	device_id int,
	event_date date,
	games_played int
);

insert into activity values
(1, 2, '2016-03-01', 5),
(1, 2, '2016-03-02', 6),
(1, 3, '2017-06-25', 1),
(3, 1, '2016-03-02', 0),
(3, 4, '2018-07-03', 5);

select round(select count(player_id) from (
	select
		player_id,
		event_date,
		lag(event_date)
			over(partition by player_id order by event_date) as next_date,
		rank() over(partition by player_id order by event_date) as login_times
	from activity
) as temp where datediff(event_date, next_date) = 1 and login_times = 2
/
select count(player_id) from (
	select player_id from activity group by player_id
) as temp, 2) as fraction;

select count(player_id) from (
	select player_id from activity group by player_id
) as temp

select count(distinct player_id) from activity

lag(event_date) over(partition by player_id order by event_date) as next_date

rank() over(partition by player_id order by event_date) as login_times

select
	player_id,
	event_date,
	lag(event_date)
		over(partition by player_id order by event_date) as next_date,
	rank() over(partition by player_id order by event_date) as login_times
from activity

select count(player_id) from (
	select
		player_id,
		event_date,
		lag(event_date)
			over(partition by player_id order by event_date) as next_date,
		rank() over(partition by player_id order by event_date) as login_times
	from activity
) as temp where datediff(event_date, next_date) = 1 and login_times = 2

select round((
	(select count(player_id) from (
		select
			player_id,
			datediff(event_date, min(event_date) over(partition by player_id)) as diff
		from activity
	) as temp where diff = 1) / (select count(distinct player_id) from activity)
), 2) as fraction;

select
	player_id,
	datediff(event_date, min(event_date) over(partition by player_id)) as diff
from activity

select count(player_id) from (
	select
		player_id,
		datediff(event_date, min(event_date) over(partition by player_id)) as diff
	from activity
) as temp where diff = 1

with temp as (
	select
		player_id,
		datediff(event_date, min(event_date) over(partition by player_id)) as diff
	from activity
) select round(
	sum(case diff when 1 then 1 else 0 end) /
	count(distinct player_id),
2) as fraction from temp;

sum(case diff when 1 then 1 else 0 end)

count(distinct player_id)

select round(avg(event_date is not null), 2) as fraction from (
	select player_id, min(event_date) as first_login from activity
	group by player_id
) temp left join activity
on temp.player_id = activity.player_id
and datediff(event_date, first_login) = 1;

select player_id, min(event_date) as first_login from activity
group by player_id

select * from (
	select player_id, min(event_date) as first_login from activity
	group by player_id
) temp left join activity
on temp.player_id = activity.player_id
and datediff(event_date, first_login) = 1;

create table employee(
	id int,
	month int,
	salary int
);

insert into employee values
(1, 1, 20),
(2, 1, 20),
(1, 2, 30),
(2, 2, 30),
(3, 2, 40),
(1, 3, 40),
(3, 3, 60),
(1, 4, 60),
(3, 4, 70),
(1, 7, 90),
(1, 8, 90);

SELECT
	id,
	MONTH,
	salary,
	lead( salary, 2 , 0) over ( PARTITION BY id ORDER BY `month` ) lead_salary
FROM
	employee;

SELECT
	id,
	MONTH,
	salary,
	lag( salary, 2 , 0) over ( PARTITION BY id ORDER BY `month` ) lag_salary
FROM
	employee;

SELECT
	*,
	rank() over ( PARTITION BY id ORDER BY `month` ) as rk,
	percent_rank() over ( PARTITION BY id ORDER BY `month` ) as p_rk
FROM
	employee;

SELECT
	*,
	rank() over ( PARTITION BY id ORDER BY `month` ) AS rk,
	cume_dist() over ( PARTITION BY id ORDER BY `month` ) AS c_rk
FROM
	employee;

SELECT
	*,
	first_value(salary) over ( PARTITION BY id ORDER BY `month`) AS first_val
FROM
	employee;

SELECT
	*,
	last_value(salary) over ( PARTITION BY id ORDER BY `month`) AS last_val
FROM
	employee;

SELECT
	*,
	nth_value(salary, 2) over ( PARTITION BY id ORDER BY `month`) AS nth_val
FROM
	employee;

SELECT
	*,
	ntile(2) over ( PARTITION BY id ORDER BY `month`) AS ntile_val
FROM
	employee;

select *|字段名 [as 别名] from 表名 [where 子句] [group by 子句] [having 子句] [order by 子句] [limit 子句];

create table <table_name> (
   id int primary key,
   name varchar(20)
);

alter table <table_name> drop primary key;

create table <table_name> (
    id int primary key auto_increment,
    name varchar(20)
);

-- 等价于

create table <table_name> (
    id int auto_increment,
    name varchar(20),
    primary key(name) USING BTREE
);

alter table <table_name> auto_increment = 100;

create table <table_name> (
   id int,
   name varchar(20) unique index
);

-- 等价于

create table <table_name> (
    id int,
    name varchar(20),
    UNIQUE INDEX name(name) USING BTREE
);

create table <table_name> (
   id int,
   name varchar(20) not null,
   gender char(2)
);

create table <table_name> (
   id int,
   name varchar(20),
   location varchar(50) default "射手"
);

create table employee (
	id int,
	name varchar(255),
	department varchar(255),
	managerId int
);

insert into employee values
(101, 'John', 'A', null),
(102, 'Dan', 'A', 101),
(103, 'James', 'A', 101),
(104, 'Amy', 'A', 101),
(105, 'Anne', 'A', 101),
(106, 'Ron', 'B', 101);

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

select managerId from employee group by managerId having count(managerId) >= 5

select name from employee, (
	select managerId from employee group by managerId having count(managerId) >= 5
) as temp where employee.id = temp.managerId;

select managerId from employee group by managerId having count(managerId) >= 5

create table scores (
	id int,
	score double
)

insert into scores values(1,3.50),(2,3.65),(3,4.00),(4,3.85),(5,4.00),(6,3.65)

select score,
	case
		when @pre=score then @rank:=@rank
		when @pre:=score then @rank:=@rank + 1
	end as 排名
from scores, (select @pre=null, @rank:=0) as init
order by score desc;

select score, dense_rank() over(order by score desc) as 排名 from scores;

create table activity (
	player_id int,
	device_id int,
	event_date date,
	games_played int
);

insert into activity values
(1, 2, '2016-03-01', 5),
(1, 2, '2016-05-02', 6),
(1, 3, '2017-06-25', 1),
(3, 1, '2016-03-02', 0),
(3, 4, '2018-07-03', 5);

select player_id, min(event_date) as first_login from activity
group by player_id;

select player_id, event_date as first_login from (
	select
		player_id,
		event_date,
		dense_rank()
		 over(partition by player_id order by event_date) as 排名
	from activity
) as temp where 排名 = 1;

CREATE TABLE sales (
	sale_id INT,
	product_id INT,
	year INT,
	quantity INT,
	price INT 
);
INSERT INTO sales ( sale_id, product_id, year, quantity, price ) VALUES
( 1, 100, 2008, 10, 5000 ),
( 2, 100, 2009, 12, 5000 ),
( 7, 200, 2011, 15, 9000 );
	
CREATE TABLE product ( 
	product_id INT, 
	product_name VARCHAR ( 10 ) 
);
INSERT INTO product ( product_id, product_name ) VALUES
( 100, 'Nokia' ),
( 200, 'Apple' ),
( 300, 'Samsung' );

select
	product_id,
	year as first_year,
	quantity,
	price
from (
	select 
		product_id,
		year,
		quantity,
		price,
		rank() over(partition by product_id order by year) as rn
	from sales
) as temp where rn = 1;

select
	product_id,
	year as first_year,
	quantity,
	price
from sales where (product_id, year) in (
	select product_id, min(year) from sales group by product_id
);

select
	sales.product_id,
	first_year,
	quantity,
	price
from (
	select product_id, min(year) first_year from sales group by product_id
) as temp 
left join sales on temp.product_id = sales.product_id 
and temp.first_year = sales.year;

create table if not exists candidate (id int, name varchar(255))
insert into candidate (id, name) values (1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

create table if not exists vote (id int, candidateId int);
insert into vote (id, candidateId) values (1, 2),
(2, 4),
(3, 3),
(4, 2),
(5, 5);

select name from candidate where id = (
	select candidateId from vote
	group by candidateId
	order by count(candidateId) desc limit 1
);

select name from candidate, (
	select
		distinct candidateId,
		count(candidateId) over(partition by candidateId) as counts
	from vote order by counts desc limit 1
) as temp where candidate.id = temp.candidateId;

select name from candidate join vote on candidate.id = vote.candidateId
group by name order by count(name) desc limit 1;

select abs(2); // 2
select abs(-2); // 2
select abs(0); // 0
select abs(-0); // 0

select abs('11'); // 11
select abs('123dd'); // 123
select abs('dd123'); // 0
select abs('mysql'); // 0
select abs(null); // null
select abs(1/0); // null
select abs('2022-01-22'); // 2022
select abs(true); // 1
select abs(false); // 0
select abs(CURRENT_TIMESTAMP()); // 20220128155334

select mod(4, 3); // 1
select mod(4, 2); // 0
select mod(3, 4); // 3

select mod(4, 0); // null
select mod(4, 'e'); // null
select mod(4, '3'); // 1
select mod(4, '123d'); // 1
select mod(4, true); // 0
select mod(4, 1/0); // null
select mod(4, null); //null

select round(3.24, 1); // 3.2

select round(3.23, null); // null
select round(3.23, 1/0); // null

select concat('abc', 123); // 'abc123'

select concat('abc', true); // 'abc1'
select concat('abc', false); // 'abc0'
select concat('abc', null); // null
select concat('abc', 1/0); // null
select concat('abc', CURRENT_TIMESTAMP()); // abc2022-01-29 14:52:01

select length('abc');  // 3
select length(23);  // 2
select legnth(1.2); // 3
select length("你好"); // 6

select length(true); // 1，true 是 1 所以结果是 1
select length(null); // null

select char_length('abc');  // 3
select char_length('你好');  // 2
select char_length(123); // 3
select char_length(true);  // 1
select char_legnth(null);  // null

select lower('FF'); // 'ff'
select lower(1); // 1
select lower(true); // 1
select lower(null); // null
select lower(1/0); // null
select lower('你好'); // '你好'

select replace('abc', 'c', 'd'); // 'abd'
select replace('abc', 'd', 'D'); // 'abc'
select replace('abc', 'c', true); // 'ab1'
select replace('abc', 'c', null); // null
select replace(true, 1, 'abc'); // 'abc'
select replace(true, true, 'abc'); // 'abc'
select replace(true, 'a', 34); // 1

select substring('abcd', 1, 3); // 'abc'
select substring('abcd', 0, 3); //  空
select substring('abcd', 3, 1); // 'c'
select substring('abcd', 3, -1); //  空
select substring('abcd', 1, 1); // 'a'
select substring('abcd', 2, 2); // 'bc'
select substring('abcd', -1, 1); // 'd'
select substring('abcd', 1, null); // null
select substring('abcd', 1, 'd'); //  空

select current_time(); // 12:01:34
select current_date(); // 2022-01-30
select current_timestamp(); // 2022-01-30 12:01:34
select extract(year from '2022-01-30 12:01:34'); // 2022
select date('2022-01-30 12:01:34'); // 2021-01-30
select year('2022-01-30 12:01:34'); // 2022
select month('2022-01-30 12:01:34'); // 01
select day('2022-01-30 12:01:34'); // 30
select hour('2022-01-30 12:01:34'); // 12
select minute('2022-01-30 12:01:34'); // 01
select second('2022-01-30 12:01:34'); // 34

select cast(12.3 as signed); // 12
select cast(12.3 as char); // 12.1

select cast('12' as int); // 报错
select cast('12' as float); // 12

select cast('1234.56789' as decimal(8, 2)); // 1234.57
select cast('1234.56789' as decimal(6, 2)); // 1234.57

select coalesce(null, null, 2);  // 2
select coalesce(null, 1); // 1

create table employee (
	id int primary key auto_increment,
	name varchar(255),
	salary int,
	departmentId int
);
create table department (
	id int primary key auto_increment,
	name varchar(255)
);

insert into employee (name, salary, departmentId) values
('joe', 85000, 1),
('henry', 80000, 2),
('sam', 60000, 2),
('max', 90000, 1),
('janet', 69000, 1),
('randy', 85000, 1),
('will', 70000, 1);

insert into department(name) values('it'),('sales');

select department.id, employee.name, employee.salary from (
	select te.departmentId, te.salary,
		case ①
			when @pre=departmentId then @rank:=@rank + 1
			when @pre:=departmentId then @rank:=1
		end as 排名
	from (select @pre:=null, @rank:=0) tt,
	(
		select departmentId, salary from employee
		group by departmentId, salary
		order by departmentId, salary desc
	) te
) t
inner join department on t.departmentId = department.id
inner join employee on t.departmentId = employee.departmentId
and employee.salary = t.salary and 排名 <= 3
order by t.departmentId, t.salary desc;

create table sequence (id int, name varchar(10), age int);

insert into sequence values
(1, '张三', 19),
(2, '李四', 20),
(3, '王五', 30),
(4, '赵六', 24),
(5, '小红', 19);

select *, row_number() over(order by age) as 排名 from sequence;

select *, rank() over(order by age) as 排名 from sequence;

select *, rank() over(order by age, id desc) as 排名 from sequence;

select *, dense_rank() over(order by age) as 排名 from sequence;

create table insurance (
	pid int, 
	tiv_2015 float, 
	tiv_2016 float, 
	lat float, 
	lon float
);

insert into insurance (pid, tiv_2015, tiv_2016, lat, lon) values 
('1', '10', '5', '10', '10'),
('2', '20', '20', '20', '20'),
('3', '10', '30', '20', '20'),
('4', '10', '40', '40', '40');

select round(sum(tiv_2016), 2) tiv_2016 from (
	select
		tiv_2016,
		count(*) over(partition by tiv_2015) count_tiv_2015,
		count(*) over(partition by lat, lon) count_lat_lon
	from insurance
) as temp where count_lat_lon = 1 and count_tiv_2015 > 1

select round(sum(tiv_2016), 2) tiv_2016 from insurance where tiv_2015 in (
	select tiv_2015 from insurance group by tiv_2015 having count(*) > 1
) and concat(lat, lon) in (
	select 
		concat(lat, lon)
	from insurance group by lat, lon having count(*) = 1
);

create table courses (
	student varchar(255),
	class varchar(255)
)

insert into courses values
('A', 'Math'),
('B', 'English'),
('C', 'Math'),
('D', 'Biology'),
('E', 'Math'),
('F', 'Computer'),
('G', 'Math'),
('H', 'Math'),
('I', 'Math');

select class from courses group by class having count(*) >= 5;

select class from (
	select class, count(*) as num from courses group by class
) as c where num >= 5;

create table trips (
	id int,
	client_id int, 
	driver_id int, 
	city_id int, 
	status char(255), 
	request_at date
);
insert into trips values(1, 1, 10, 1, 'completed', '2013-10-01');
insert into trips values(2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01');
insert into trips values(3, 3, 12, 6, 'completed', '2013-10-01');
insert into trips values(4, 4, 13, 6, 'cancelled_by_client', '2013-10-01');
insert into trips values(5, 1, 10, 1, 'completed', '2013-10-02');
insert into trips values(6, 2, 11, 6, 'completed', '2013-10-02');
insert into trips values(7, 3, 12, 6, 'completed', '2013-10-02');
insert into trips values(8, 2, 12, 12, 'completed', '2013-10-03');
insert into trips values(9, 3, 10, 12, 'completed', '2013-10-03');
insert into trips values(10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');

create table users (
	users_id int,
	banned char(255), 
	role char(255)
);
insert into users value(1, 'No', 'client');
insert into users value(2, 'Yes', 'client');
insert into users value(3, 'No', 'client');
insert into users value(4, 'No', 'client');
insert into users value(10, 'No', 'driver');
insert into users value(11, 'No', 'driver');
insert into users value(12, 'No', 'driver');
insert into users value(13, 'No', 'driver');

select 
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips
join users u1 on trips.client_id = u1.users_id and u1.banned = 'No'
join users u2 on trips.driver_id = u2.users_id and u2.banned = 'No'
where trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

if (client_id = users_id or driver_id = users_id) and users_id没有被禁止 
...

select * from trips join users on (trips.client_id = users.users_id 
or trips.driver_id = users.users_id) and users.banned = 'No';

if(client_id = users_id and users_id没有禁止 
and driver_id = users_id and users_id没有被禁止)
...

select * from trips
join users u1 on trips.client_id = u1.users_id and u1.banned = 'No'
join users u2 on trips.driver_id = u2.users_id and u2.banned = 'No';

select
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips
left join (
	select users_id from users where banned = 'Yes'
) as a1 on trips.client_id = a1.users_id
left join(
	select users_id from users where banned = 'Yes'
) as a2 on trips.driver_id = a2.users_id
where a1.users_id is null and a2.users_id is null
and trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

select users_id from users where banned = 'Yes';

select * from trips, (
	select users_id from users where banned = 'Yes'
) as a 
where trips.client_id != a.users_id and trips.driver_id != a.users_id

select * from trips where
trips.client_id in (select users_id from users where banned = 'No')
and
trips.driver_id in (select users_id from users where banned = 'No');

select 
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips 
left join (
	select users_id from users where banned = 'Yes'
) as a1 on trips.client_id = a1.users_id
left join (
	select users_id from users where banned = 'Yes'
) as a2 on trips.driver_id = a2.users_id
where a1.users_id is null and a2.users_id is null
and trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

select 
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips 
join (
	select users_id from users where banned = 'No'
) as a1 on trips.client_id = a1.users_id
join (
	select users_id from users where banned = 'No'
) as a2 on trips.driver_id = a2.users_id
where trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

CREATE TABLE sales (
	sale_id INT,
	product_id INT,
	year INT,
	quantity INT,
	price INT 
);
INSERT INTO sales ( sale_id, product_id, year, quantity, price ) VALUES
( '1', '100', '2008', '10', '5000' ),
( '2', '100', '2009', '12', '5000' ),
( '7', '200', '2011', '15', '9000' );
	
CREATE TABLE product ( 
	product_id INT, 
	product_name VARCHAR ( 10 ) 
);
INSERT INTO product ( product_id, product_name ) VALUES
( '100', 'Nokia' ),
( '200', 'Apple' ),
( '300', 'Samsung' );

select product_name, year, price from sales join product using(product_id)

select 
	product_name,
	year, 
	price 
from sales, product where sales.product_id = product.product_id

create table numbers(
	num int,
	frequency int
)

insert into numbers values
(0, 7),
(1, 1),
(2, 3),
(3, 1);

select avg(num) as median from (
	select
		num,
		sum(frequency) over(order by num desc) desc_frequency,
		sum(frequency) over(order by num asc) asc_frequency,
		sum(frequency) over() total_frequency
	from numbers
) as temp
where desc_frequency >= total_frequency / 2
and asc_frequency >= total_frequency / 2;

select avg(num) as median from (
	select
		num,
		(
			select sum(frequency) from numbers n2 where n1.num >= n2.num
		) asc_frequency,
		(
			select sum(frequency) from numbers n3 where n1.num <= n3.num
		) desc_frequency,
		(select sum(frequency) from numbers) total_frequency
	from numbers n1
) temp
where asc_frequency >= total_frequency / 2
and desc_frequency >= total_frequency / 2;

select avg(num) as median from (
	select n1.num from numbers n1 join numbers n2 group by n1.num
	having sum(if(n1.num >= n2.num, n2.frequency, 0)) >= sum(n2.frequency) / 2
	and sum(if(n1.num <= n2.num, n2.frequency, 0)) >= sum(n2.frequency) / 2
) as temp;

create table student(
	student_id int,
	student_name varchar(255),
	gender varchar(255),
	dept_id int
);

insert into student values
(1, 'Jack', 'M', 1),
(2, 'Jane', 'F', 1),
(3, 'Mark', 'M', 2);

create table department(
	dept_id int,
	dept_name varchar(255)
);

insert into department values
(1, 'Engineering'),
(2, 'Science'),
(3, 'Law');

SELECT
	dept_name,
	count( student_id ) AS student_number 
FROM
	department
	LEFT JOIN student ON department.dept_id = student.dept_id 
GROUP BY
	dept_name 
ORDER BY
	dept_name DESC

create table logs(id int, num int)
insert into logs values
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2);

select distinct l.num from (
	select num,
	if(@n = num, @c:=@c + 1, @c:=1) as c,
	@n:=num from logs, (select @n:=0, @c:=0) as init
) as l where l.c >= 3;

select distinct a.num from logs a
left join logs b on a.id = b.id + 1
left join logs c on a.id = c.id + 2
where a.num = b.num and a.num = c.num;

create table salary (
	id int primary key,
	name varchar(255),
	sex varchar(255),
	salary int
)

insert into salary values
(1, 'A', 'm', 2500),
(2, 'B', 'f', 1500),
(3, 'C', 'm', 5500),
(4, 'D', 'f', 500);

update salary set sex = case sex when 'm' then 'f' else 'm' end;

update salary set sex = if(sex = 'm', 'f', 'm');

update salary set sex = char(ascii(sex) ^ ascii('m') ^ ascii('f'));

select date_add('2022-01-03', interval 1 day); // 2022-01-04

select date_add('2022-01-33', interval 1 day); // null

select date_add('2022-01-03', inteval 1 minute); // 2022-01-03 00:01:00

select date_add('2022-01-03', interval -1 day); // 2022-01-02

select date_add('2022-01-03', interval '1-1' day_hour); // 2022-01-04 01:00:00
-----
select date_add('2022-01-03', interval '1-1' day_minute); // 2022-01-03 01:01:00
select date_add('2022-01-03', interval '1-1' hour_minute); // 2022-01-03 01:01:00

select date_add('2022-01-03', '-1-1', day_hour); // 2022-01-01 23:00:00

select adddate('2022-01-03', 1); // 2022-01-04

select datediff('2021-12-31', '2022-01-02'); // -2

select datediff('2021-12-32', '2022-01-02'); // null

select datediff('2021-12-31 23:59:59', '2022-01-02 00:00:00'); // -2

select timediff('2022-01-01 00:00:00', '2022-01-02 00:00:00'); // -24:00:00

select timediff('2021-11-10 00:00:00', '2022-01-02 00:00:00'); // -838:59:59

select timediff('2022-01-01 40:00:00', '2022-01-02 00:00:00'); // null
select timediff('2022-01-41 00:00:00', '2022-01-02 00:00:00'); // null

select timediff('2022-01-31', '2022-01-02'); // 00:00:00
select timediff('2022-01-02', '2021-12-31'); // 00:00:01
select timediff('2021-12-31', '2022-01-02'); // -00:00:01

create table surveyLog (
	id int,
	action varchar(255),
	question_id int,
	answer_id int,
	q_num int,
	timestamp int
);

insert into surveyLog values
(5, 'show', 285, null, 1, 123),
(5, 'answer', 285, 124124, 1, 124),
(5, 'show', 369, null, 2, 125),
(5, 'skip', 369, null, 2, 126);

select question_id as survey_log from (
	select 
		question_id,
		sum(case action when 'answer' then 1 else 0 end) / 
		sum(case action when 'show' then 1 else 0 end) as rate
	from surveyLog group by question_id order by rate desc
) as temp limit 1;

select action_answer.question_id as survey_log from (
	select question_id, count(*) as answer_count from surveyLog
	where action = 'answer' group by question_id
) as action_answer join (
	select question_id, count(*) as show_count from surveyLog
	where action = 'show' group by question_id
) as action_show using(question_id)
order by answer_count / show_count desc limit 1;

select question_id as survey_log from surveyLog 
group by question_id 
order by avg(action = 'answer') desc limit 1;

select question_id as survey_log from surveyLog
group by question_id 
order by count(answer_id) desc limit 1;

create table activity (
	player_id int,
	device_id int,
	event_date date,
	games_played int
);

insert into activity values
(1, 2, '2016-03-01', 5),
(1, 2, '2016-05-02', 6),
(1, 3, '2017-06-25', 1),
(3, 1, '2016-03-02', 0),
(3, 4, '2018-07-03', 5);

select activity.player_id, activity.device_id from activity, (
	select player_id, min(event_date) as first_login
	from activity group by player_id
) as temp
where activity.player_id = temp.player_id
and activity.event_date = temp.first_login;

select player_id, device_id from (
	select
		player_id,
		device_id,
		dense_rank() over(partition by player_id order by event_date) as 排名
	from activity
) as temp where temp.排名 = 1;

select player_id, device_id from activity a1 where event_date <= all(
	select a2.event_date from activity a2 where a1.player_id = a2.player_id
);

create table employee (
	id int primary key auto_increment,
	salary int
);

insert into employee (salary) values(100),(200),(300);

create function getNthHighestSalary(N int) returns int
begin
	return(
		select e.salary from (
			select salary,
				case when id, salary is not null then @rank:=@rank + 1 end as 排名
			from (select @rank:=0) as init, (
				select salary from employee
				group by id, salary
				order by salary desc
			) as t
		) as e where e.排名 = N
	)
end

create function getNthHighestSalary(N int) returns int
declare m int;
set m = N - 1;
begin
	return(
		select salary from employee group by salary order by salary desc limit m, 1
	);
end

create function getNthHighestSalary(N int) returns int
begin
	return (
		select salary from (
			select id, salary,
				@rank:= if(salary is not null, @rank + 1, @rank) as 排名
				from employee, (select @rank:=0) as init
				group by id, salary
				order by salary desc
		) as e where 排名 = N
	);
end

create table stadium (
	id int,
	visit_date date,
	people int
)

insert into stadium values
(1, '2017-01-01', 10),
(2, '2017-01-02', 109),
(3, '2017-01-03', 150),
(4, '2017-01-04', 99),
(5, '2017-01-05', 145),
(6, '2017-01-06', 1455),
(7, '2017-01-07', 199),
(8, '2017-01-09', 188);

select * from (
	select *, count(*) over(partition by diff) as counts from (
		select *, id - row_number() over(order by id) as diff from stadium
		where people >= 100
	) as base
) as s where counts >= 3;

select * from (
	select *, (id - (@rrk:=@rrk + 1)) as diff
	from stadium, (select @rrk:=0) as init where people >= 100
) as s1 where diff in (
	select diff from (
		select (id - (@rk:=@rk + 1)) as diff
		from stadium, (select @rk:=0) as init where people >= 100
	) as s2 group by diff having count(*) >= 3
);

create table weather (
	id int primary key auto_increment,
	recordDate date,
	temperature int
);

insert into weather(recordDate, temperature) values
('2015-01-01', 10),
('2015-01-02', 25),
('2015-01-03', 20),
('2015-01-04', 30);

select weather.id from weather join weather w1
on datediff(weather.recordDate, w1.recordDate) = 1
and weather.temperature > w1.temperature;

select weather.id from weather join weather w1
on weather.recordDate = adddate(w1.recordDate, interval 1 day)
and weather.temperature > w1.temperature;

select id from (
	select
	temperature,
	recordDate ,
	lead(id, 1) over(order by recordDate) as id,
	lead(recordDate, 1) over(order by recordDate) as 'nextDate',
	lead(temperature, 1) over(order by recordDate) as 'nextTemp'
	from weather
) temp
where nextTemp > temperature and datediff(nextDate, recordDate) = 1;

select
	temperature,
	recordDate ,
	lead(id, 1) over(order by recordDate) as nextId,
	lead(recordDate, 1) over(order by recordDate) as 'nextDate',
	lead(temperature, 1) over(order by recordDate) as 'nextTemp'
from weather;

create table employee (
	empId int,
	name varchar(255),
	supervisor int,
	salary int
);
insert into employee values
(1, 'John', 3, 1000),
(2, 'Dan', 3, 1000),
(3, 'Brad', null, 1000),
(4, 'Thomas', 3, 1000);

create table bonus(
	empId int,
	bonus int
);
insert into bonus values
(2, 500),
(4, 2000);

select name, bonus from employee left join bonus on employee.empId = bonus.empId
where ifnull(bonus, 0) < 1000;

create table student (
	name varchar(50),
	continent varchar(7)
);

insert into student (name, continent) values
('Jane', 'America'),
('Pascal', 'Europe'),
('Xi', 'Asia'),
('Jack', 'America');

select America, Asia, Europe from (
	select
		name as America,
		row_number() over(order by name) as rn
	from student where continent = 'America'
) temp1 left join (
	select
		name as Europe,
		row_number() over(order by name) as rn
	from student where continent = 'Europe'
) temp2 on temp1.rn = temp2.rn left join (
	select
		name as Asia,
		row_number() over(order by name) as rn
	from student where continent = 'Asia'
) temp3 on temp1.rn = temp3.rn;

select
	America, Asia, Europe
from (select @america:=0, @europe:=0, @asia:=0) as init, (
	select
		name as America,
		@america:= @america + 1 as america_id
	from student where continent = 'America' order by name
) t1 left join (
	select
		name as Europe,
		@europe:= @europe + 1 as europe_id
	from student where continent = 'Europe' order by name
) t2 on america_id = europe_id left join (
	select
		name as Asia,
		@asia:= @asia + 1 as asia_id
	from student where continent = 'Asia' order by name
) t3 on america_id = asia_id;

select
	max(case continent when 'America' then name else null end) America,
	max(case continent when 'Asia' then name else null end) Aisa,
	max(case continent when 'Europe' then name else null end) Europe
from (
	select
		*,
		row_number() over(partition by continent order by name) as rn
	from student
) temp group by rn;

create table person (
	personId int,
	firstName varchar(255),
	lastName varchar(255)
);

create table address (
	addressId int primary key,
	personId int,
	city varchar(255),
	state varchar(255)
);

select FirstName, LastName, City, State from Person
left join Address on Person.PersonId = Address.PersonId;

create table world (
	name varchar(255),
	continent varchar(255),
	area int,
	population int,
	gdp bigint
);

insert world values
('afghanistan', 'asia', 652230, 25500100, 2034000000),
('albania', 'europe', 28748, 2831741, 12960000000),
('algeria', 'africa', 2381741, 37100000, 188681000000),
('andorra', 'europe', 468, 78115, 3712000000),
('angola', 'africa', 1246700, 20609294, 100990000000);

select name, population, area from world where
area >= 3000000 or population >= 25000000;

select name, population, area from world where area >= 3000000
union
select name, population, area from world where population >= 25000000;

create table employee (
	id int,
	name varchar(255),
	salary int,
	managerId int
);

insert into employee values 
(1, 'Joe', 70000, 3),
(2, 'Henry', 80000, 4),
(3, 'Sam', 60000, null),
(4, 'Max', 90000, null);

select employee.name from employee left join employee e
on employee.managerId = e.id
where employee.salary > e.salary;

create table cinema (
	id int,
	movie varchar(255),
	description varchar(255),
	rating float
);

insert into cinema values
(1, 'War', 'great 3D', 8.9),
(2, 'Science', 'fiction', 8.5),
(3, 'irish', 'boring', 6.2),
(4, 'Ice song', 'Fantacy', 8.6),
(5, 'House card', 'Insteresting', 9.1);

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

create table customer(
	id int,
	name varchar(255),
	referee_id int
);

insert into customer values
(1, 'Will', null),
(2, 'Jane', null),
(3, 'Alex', 2),
(4, 'Bill', null),
(5, 'Zack', 1),
(6, 'Mark', 2);

select name from customer where ifnull(referee_id, 0) != 2 or referee_id != 2;

create table t1 (id int, value int);
create table t2 (id int, value int);

insert into t1 values(1, 10), (2, 300), (3, 40), (4, 60), (5, 70), (6, 80);
insert into t2 values(1, 100), (2, 300), (3, 40), (4, 600), (5, 70), (6, 800);

select * from t1 where value <= all(
	select value from t2
);

select * from t1 where value <= any(
	select value from t2
);

select * from t1 where value != some(
	select value from t2
);

create table person (
	id int,
	email varchar(255)
);
insert into person values(1, '[email protected]'), (2, '[email protected]'), (3, '[email protected]');

select email from person group by email having count(email) > 1;

select t.email from (
	select email, count(email) num from person group by email
) t where t.num > 1;

with temp as (
	select email, count(email) num from person group by email
) 
select email from temp where num > 1;

select distinct person.email from person
left join person temp on person.email = temp.email
where person.id != temp.id;