## 1355. Activity Participants

Question:

Solution:

``````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
)``````

## 1364. Number of Trusted Contacts of a Customer

Question:

Solution:

``````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``````

## 1369. Get the Second Most Recent Activity

Question:

Solution:

``````select
u.activity,
u.startDate,
u.endDate
from (
select
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
)``````

## 1378. Replace Employee ID with the Unique Identifier

Question:

Solution:

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