## 612. Shortest Distance in a Plane

Question:

Solution:

``````select
round(min(sqrt(power((x1-x2),2) + power((y1-y2),2))),2) as shortest
from(
select
p1.x as x1,
p1.y as y1,
p2.x as x2,
p2.y as y2
from point_2d p1
join point_2d p2
on p1.x <> p2.x
or p1.y <> p2.y
) p``````

## 613. Shortest Distance in a Line

Question:

Solution:

``````select
c.diff as shortest
from(
select
b.x,
b.diff,
row_number() over(order by b.diff) as r
from(
select
x,
from(
select
x,
from point
) a
) b
where b.diff is not null
) c
where c.r = 1``````

## 614. Second Degree Follower

Question:

Solution:

``````select
followee as follower,
count(distinct follower) as num
from follow
where followee in (
select follower
from follow
)
group by 1``````

## 615. Average Salary: Department vs Company

Question:

Solution:

``````select
b.pay_month,
b.department_id,
b.comparison
from(
select
d.pay_month,
d.department_id,
d.avg_dept_amount,
c.avg_company_amount,
case
when d.avg_dept_amount< c.avg_company_amount then 'lower'
when d.avg_dept_amount= c.avg_company_amount then 'same'
when d.avg_dept_amount> c.avg_company_amount then 'higher'
else null
end as comparison
from(
select
substr(s.pay_date,1,7) as pay_month,
e.department_id,
avg(amount) as avg_dept_amount
from salary s
left join employee e
on s.employee_id = e.employee_id
group by 1,2
) d
left join (
select
substr(pay_date,1,7) as pay_month,
avg(amount) as avg_company_amount
from salary
group by 1
) c
on d.pay_month = c.pay_month
) b
order by 1 desc, 2``````