Day: September 16, 2019

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

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

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

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