Leetcode SQL (#584 – #586)

select 
name
from customer
where referee_id <> 2 
or referee_id is null

select 
round(sum(i.tiv_2016),2) as tiv_2016
from insurance i
/* tiv_2015 value with >=2 policy holders */
join (
    select 
    tiv_2015, 
    count(distinct pid) as policy_holders
    from insurance
    group by 1
    having count(distinct pid) >= 2
) i2 
    on i.tiv_2015 = i2.tiv_2015
/* unique lat/lon combination */
join (
    select 
    lat,
    lon, 
    count(distinct pid) as policy_holders
    from insurance
    group by 1,2
    having count(distinct pid) = 1
) i3 
    on i.lat = i3.lat 
    and i.lon = i3.lon

select 
b.customer_number
from(
    select 
    a.customer_number, 
    dense_rank() over(order by a.number_of_orders desc) as r
    from(
        select 
        customer_number, 
        count(distinct order_number) as number_of_orders
        from orders
        group by 1
    ) a
) b
where b.r = 1