1264. Page Recommendations
Question:

Solution:
select distinct
l.page_id as recommended_page
from Likes l
/* user_id = 1's friend user_ids */
where l.user_id in (
select distinct user_id
from(
select
user2_id as user_id
from Friendship
where user1_id = 1
union
select
user1_id as user_id
from Friendship
where user2_id = 1
) u
)
/* exclude pages that user_id = 1 like */
and l.page_id not in (
select distinct
page_id
from Likes
where user_id = 1
)
1270. All People Report to the Given Manager
Question:

Solution:
select distinct
a.employee_id
from(
select
e.employee_id,
e.employee_name,
e.manager_id,
m.manager_id as manager_id2,
m2.manager_id as manager_id3
from Employees e
left join (
select distinct
employee_id,
manager_id
from Employees
) m
on e.manager_id = m.employee_id
left join (
select distinct
employee_id,
manager_id
from Employees
) m2
on m.manager_id = m2.employee_id
/* exclude head of the company */
where (m2.manager_id = 1
and e.employee_id <> 1)
) a
order by 1
1280. Students and Examinations
Question:

Solution:
select
s.student_id,
s.student_name,
s.subject_name,
ifnull(b.attended_exams,0) as attended_exams
from (
select
s.student_id,
s.student_name,
sub.subject_name
from Students s
cross join Subjects sub
)s
left join (
select
student_id,
student_name,
subject_name,
sum(exam) as attended_exams
from(
select
e.student_id,
s.student_name,
e.subject_name,
1 as exam
from Examinations e
left join Students s
on e.student_id = s.student_id
) a
group by 1,2,3
) b
on (s.student_id = b.student_id
and s.subject_name = b.subject_name)
order by 1,3
1285. Find the Start and End Number of Continuous Ranges
Question:

Solution:
select
l2.start_id,
l2.end_id
from(
select
grp,
min(log_id) as start_id,
max(log_id) as end_id
from(
select
log_id,
row_number() over(order by log_id) as r,
/* for consecutive numbers, their row numbers will be the same as the log_id */
log_id - (row_number() over(order by log_id)) as grp
from Logs
) l
group by 1
) l2
order by 1
One thought on “Leetcode SQL (#1264, #1270, #1280, #1285)”