Scribd
Upload
4 views4 pages

10 SQL Intermediate MockTests HackerRank

The document contains 10 SQL intermediate mock tests designed in HackerRank style, each with specific schemas and requirements related to employee, customer, and sales data. Each test includes a SQL query solution that addresses the given requirement, such as finding the highest salary in each department or listing customers without orders. The document serves as a practice resource for individuals looking to improve their SQL skills.

Uploaded by

ndhieu110105
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views4 pages

10 SQL Intermediate MockTests HackerRank

The document contains 10 SQL intermediate mock tests designed in HackerRank style, each with specific schemas and requirements related to employee, customer, and sales data. Each test includes a SQL query solution that addresses the given requirement, such as finding the highest salary in each department or listing customers without orders. The document serves as a practice resource for individuals looking to improve their SQL skills.

Uploaded by

ndhieu110105
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

10 SQL Intermediate Mock Tests (HackerRank

Style)
M■i ■■ g■m schema, yêu c■u và ph■n ■áp án ■ cu■i tài li■u.
■■ 1
Schema: Employees, Departments, Salaries
Yêu c■u: Tìm nhân viên có m■c l■■ng m■i nh■t cao nh■t ■ m■i phòng ban. N■u hòa, l■y
employee_id nh■ h■n.

■■ 2
Schema: Employees, Salaries
Yêu c■u: Tính previous_salary và increase theo t■ng nhân viên qua các tháng.

■■ 3
Schema: Customers, Orders
Yêu c■u: Li■t kê khách hàng ch■a t■ng ■■t hàng.

■■ 4
Schema: Products
Yêu c■u: L■y Top 3 s■n ph■m doanh s■ cao nh■t trong m■i category.

■■ 5
Schema: Employees
Yêu c■u: Tìm nhân viên có l■■ng l■n h■n m■c l■■ng trung bình c■a phòng ban.

■■ 6
Schema: Users, Logins
Yêu c■u: Tìm các l■n ■■ng nh■p liên ti■p cách nhau ■úng 1 ngày.

■■ 7
Schema: Orders
Yêu c■u: Tính running total doanh thu theo t■ng khách hàng.

■■ 8
Schema: Orders
Yêu c■u: L■y ■■n hàng g■n nh■t c■a m■i khách hàng.

■■ 9
Schema: Sales
Yêu c■u: X■p h■ng doanh thu c■a t■ng nhân viên trong t■ng n■m.

■■ 10
Schema: Students, Scores
Yêu c■u: Tìm ■i■m cao th■ 2 c■a m■i l■p h■c.
■ÁP ÁN
■■ 1
WITH latest AS (
SELECT employee_id,salary,
ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary_month DESC) rn
FROM Salaries),
r AS (
SELECT d.department_name,e.employee_name,[Link],
ROW_NUMBER() OVER(PARTITION BY d.department_id ORDER BY [Link] DESC,e.employee_id) rk
FROM latest l
JOIN Employees e ON e.employee_id=l.employee_id
JOIN Departments d ON d.department_id=e.department_id
WHERE rn=1)
SELECT department_name,employee_name,salary
FROM r WHERE rk=1;

■■ 2
SELECT employee_id,salary_month,salary,
LAG(salary) OVER(PARTITION BY employee_id ORDER BY salary_month) previous_salary,
salary - LAG(salary) OVER(PARTITION BY employee_id ORDER BY salary_month) increase
FROM Salaries;

■■ 3
SELECT c.customer_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id=o.customer_id
WHERE o.customer_id IS NULL;

■■ 4
WITH r AS (
SELECT *,
DENSE_RANK() OVER(PARTITION BY category_id ORDER BY sales DESC) rk
FROM Products)
SELECT * FROM r WHERE rk<=3;

■■ 5
SELECT e.*
FROM Employees e
JOIN (
SELECT department_id,AVG(salary) avg_sal
FROM Employees
GROUP BY department_id) d
ON e.department_id=d.department_id
WHERE [Link]>d.avg_sal;

■■ 6
WITH x AS (
SELECT user_id,login_date,
LAG(login_date) OVER(PARTITION BY user_id ORDER BY login_date) prev_day
FROM Logins)
SELECT *
FROM x
WHERE DATEDIFF(login_date,prev_day)=1;

■■ 7
SELECT customer_id,order_date,amount,
SUM(amount) OVER(
PARTITION BY customer_id
ORDER BY order_date
) running_total
FROM Orders;
■■ 8
WITH r AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY customer_id
ORDER BY order_date DESC
) rn
FROM Orders)
SELECT * FROM r WHERE rn=1;

■■ 9
SELECT year,employee_id,revenue,
RANK() OVER(
PARTITION BY year
ORDER BY revenue DESC
) rk
FROM Sales;

■■ 10
WITH r AS (
SELECT class_id,student_id,score,
DENSE_RANK() OVER(
PARTITION BY class_id
ORDER BY score DESC
) rk
FROM Scores)
SELECT class_id,student_id,score
FROM r
WHERE rk=2;

You might also like