Day: October 20, 2019

select 
f2.activity 
from(
    select 
    activity, 
    rank() over(order by cnt) as r_asc, 
    rank() over(order by cnt desc) as r_desc
    from(
        select 
        activity, 
        count(distinct id) as cnt
        from friends 
        group by 1
    ) f
) f2
/* neither max nor min number of participants */
where (r_asc <> 1 
       and r_desc <> 1 
      )

select 
i.invoice_id, 
c.customer_name, 
i.price, 
c.contacts_cnt, 
c.trusted_contacts_cnt
from invoices i
left join (
    select 
    cc.customer_id, 
    cc.customer_name, 
    count(distinct contact_email) as contacts_cnt,
    count(distinct 
          case 
            when contact_email in (
                select distinct 
                email 
                from customers
            ) then contact_email 
          else null 
          end) as trusted_contacts_cnt
    from customers cc
        left join contacts ct 
            on cc.customer_id = ct.user_id
    group by 1,2
) c 
    on i.user_id = c.customer_id
order by 1

select 
u.username, 
u.activity, 
u.startDate, 
u.endDate
from (
    select 
    username, 
    activity, 
    startDate, 
    endDate,
    rank() over (partition by username order by startDate desc) as r,
    count(*) over(partition by username) as c
    from UserActivity
    ) u
/* return the 2nd most recent activity or total activities count = 1 */
where (r = 2 
       or c = 1
      )

select 
u.unique_id, 
e.name
from Employees e
    left join EmployeeUNI u 
        on e.id = u.id