Unable to figure out SQL Query to question

Question

RELATED:

Person1_UID Person2_UID   Type
1           2             club member
1           9             family
1           11            colleague
2           4             friend
2           7             colleague
3           4             club member
3           6             colleague
3           9             friend
3           10            follower
4           6             family
4           10            family
5           8             family
6           11            colleague
7           8             colleague
8           11            friend

DAY_PACKAGE:

DID Description    UID  VID
1   Spa Day         1   19
1   Spa Day         2   20
1   Spa Day         9   17
2   Adventure Time  4   16
2   Adventure Time  6   16
3   Family Fun Day  3   18
3   Family Fun Day  4   17
3   Family Fun Day  7   18
3   Family Fun Day  10  19
4   Relaxation Date 5   20

UID: User ID
DID: Day Package ID
VID: Voucher ID

Given the above tables, find the day packages with highest participant count where all participants are related to one another as either 'family' or 'club members'.

The query should return:

DID Description Participant_Count
1   Spa Day         3
2   Adventure Time  2  

Please assist me any kind soul, thank you! I have been stuck at this for hours. T-T

I only know how to return total participant count:

SELECT Description, COUNT(DISTINCT UID) AS Participants_Count
FROM DAY_PACKAGE
GROUP BY Description
ORDER BY Participants_Count DESC

Answer 1

Solution to your problem:(MYSQL 8.0)

WITH CTE AS
(
  SELECT dp1.DID,
  dp1.Description,
  dp1.uid as uid1,
  dp2.uid as uid2,
  COUNT(*) OVER(PARTITION BY dp1.DID,dp1.UID) as total_participants,
  COUNT(CASE WHEN r.type in ('club member','family') or dp1.UID = dp2.UID THEN 1 END) 
  OVER(PARTITION BY dp1.DID,dp1.UID) as related_participants
  FROM day_package dp1
  LEFT JOIN day_package dp2
  ON dp1.did = dp2.did
  LEFT JOIN related r
  ON dp1.UID = r.person1_uid AND dp2.UID = r.person2_UID
)
SELECT DISTINCT DID,
  Description,
  related_participants as Participant_Count
FROM CTE
WHERE related_participants = total_participants
AND total_participants != 1
ORDER BY DID,Participant_Count

DB Fiddle link - https://dbfiddle.uk/11xmARuy