365 Days of Daily Coding: Day 124

For the first time, after nearly 2 years I got to meet my colleagues. It was great to see them as there are few newcomers in my team whom prior to today, I had never met. It will be a while until I go back to office again. The thing I do not like going to office is that by the time I leave home, most of the time it’s raining pretty heavily and the ride hailing services cost a lot. I paid RM14 for a ride from office to home which would normally be around RM5 during normal times. So its burns hole in my pockets when I am trying to live minimally and save as much as I can.

Puzzle 9: Matching Sets

I tried the solution in the live oracle environment here.

#copied from the repo. this is written in T-SQL
WITH temp As (
Select 1001 As EmployeeID, 'Class A' AS License FROM Dual Union All
Select 1001 As EmployeeID, 'Class B' AS License FROM Dual Union All
Select 1001 As EmployeeID, 'Class C' AS License FROM Dual Union All
Select 2002 As EmployeeID, 'Class A' AS License FROM Dual Union All
Select 2002 As EmployeeID, 'Class B' AS License FROM Dual Union All
Select 2002 As EmployeeID, 'Class C' AS License FROM Dual Union All
Select 3003 As EmployeeID, 'Class A' AS License FROM Dual Union All
Select 3003 As EmployeeID, 'Class D' AS License FROM Dual
)

WITH cte_EmployeeCount AS
(
SELECT	EmployeeID, 
		COUNT(*) AS LicenseCount
FROM	#Employees
GROUP BY EmployeeID
),
cte_EmployeeCountCombined AS
(
SELECT	a.EmployeeID AS EmployeeID, 
		b.EmployeeID AS EmployeeID2, 
		COUNT(*) AS LicenseCountCombo
FROM	#Employees a INNER JOIN
		#Employees b ON a.License = b.License
WHERE	a.EmployeeID <> b.EmployeeID
GROUP BY a.EmployeeID, b.EmployeeID
)
SELECT	a.EmployeeID, a.EmployeeID2, a.LicenseCountCombo
FROM	cte_EmployeeCountCombined a INNER JOIN
		cte_EmployeeCount b ON a.LicenseCountCombo = b.LicenseCount AND
							   a.EmployeeID <> b.EmployeeID;