SQL刷题记录 求战友互相督促交流

crystalcc 发表于 2019-5-1 01:41
近期准备国内公司的面试，对如何计算pv、留存等用户行为数据很头疼。以下两题来自拼多多2018DA春招笔试（题 ...

谢谢lz分享, query有个问题，having 应该放在group by 后面吧

xymxym 发表于 2019-6-2 07:06
谢谢lz分享, query有个问题，having 应该放在group by 后面吧

是的！小错老犯可咋整哈哈

来补一波leetcode题。

603. Consecutive Available Seats
解1.
select c1.seat_id
from cinema c1 join cinema c2
on abs(c1.seat_id - c2.seat_id) = 1 /*注意取绝对值，否则会遗漏情况*/
where c1.free = 1
and c2.free = 1
order by c1.seat_id

解2.
select c1.seat_id
from cinema c1
where c1.free = 1
and
(c1.seat_id + 1 in (select seat_id from cinema where free = 1)
  or c1.seat_id - 1 in (select seat_id from cinema where free = 1))

crystalcc 发表于 2019-6-13 23:03
来补一波leetcode题。

603. Consecutive Available Seats

I believe order by id is necessary here - someone on stackoverflow said that SQL will think you don't care about the order at all if not specified. Any comment/feedback will be appreciated!

184. Department Highest Salary
/*Solution 1*/
SELECT department.name as department, temp.name as employee, salary
FROM (SELECT departmentid, name, salary, RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) as rank from employee) temp
JOIN department
ON temp.departmentid = department.id
WHERE temp.rank = 1

/*Solution 2*/
SELECT department.name as Department, employee.name as Employee, employee.salary
FROM employee, department, (SELECT departmentid, max(salary) as max_salary
FROM employee GROUP BY departmentid ORDER BY salary DESC) temp
WHERE temp.departmentid = department.id AND employee.departmentid = temp.departmentid AND employee.salary = temp.max_salary

/*Solution 3*/
SELECT department.name, employee.name, employee.salary
FROM employee, department
WHERE employee.departmentid = department.id
AND employee.salary IN (SELECT MAX(salary) FROM employee e2 WHERE e2.departmentid = department.id)

185. Department Top Three Salaries
/*Solution 1*/
SELECT department.name as Department, temp.name as Employee, temp.salary as Salary
FROM (SELECT employee.name, employee.salary, employee.departmentid, DENSE_RANK() OVER (PARTITION BY employee.departmentid ORDER BY employee.salary DESC) as rank from employee) temp
JOIN department ON temp.departmentid = department.id
WHERE temp.rank <= 3

. Χ/*Solution 2*/
SELECT department.name as Department, e1.name as Employee, e1.salary
FROM department JOIN employee e1
ON department.id = e1.departmentid
WHERE (SELECT COUNT(DISTINCT e2.salary) FROM employee e2 WHERE e1.salary < e2.salary AND e2.departmentid = e1.departmentid) < 3

/*this one doesn’t work - LIMIT is on the total result, not grouped by department*/
SELECT department.name as Department, employee.name as Employee, employee.salary
FROM department JOIN employee
ON department.id = employee.departmentid
WHERE salary IN (SELECT salary FROM employee WHERE employee.departmentid = department.id GROUP BY departmentid ORDER BY salary LIMIT 3)

暑假基本settle好了，离秋招也越来越近了嘤嘤嘤。。。大家不要光收藏，一起认真刷题呀！

Lynn2017 发表于 2019-5-2 21:51
感觉题目都好难啊。。。

是呢，我感觉结合业务的就会很难。。。所以找到面经就要好好刷起来<3

Lenin 发表于 2019-5-3 05:59
感谢LZ 的分享！
请问下刷完LeetCode和hackrank刷什么比较好？
多谢！

地里面经吧，我觉得leetcode和hackerrank重复刷几遍也有帮助

我也打卡一下，互相监督。我是刷完就忘了，咋办。像是没做过一样