365 Days of Daily Coding: Day 58

I felt like today flew very fast. I wanted to become aware of how I spent my time throughout the day and needless to say I spent pretty much half of the day being curious of useless stuff. Night-time is when I spring into action. It is when I feel motivated to work and I do not get distracted easily.

Oh yeah! I did not post yesterday because I had migraine after a short outing.

I didn’t solve a sql challenge today. The challenge was I would say a pretty tough one. Given two rows with Employee ID and License Type, I had to find the employees with the same license type.

I think I probably spent too much time working on the solution instead of giving up and looking at the answers and trying a new solution.

Table:

Solution:

WITH Employees AS (
SELECT 1001 AS IDS, 'Class A' AS Part FROM dual UNION ALL
SELECT 1001 AS IDS, 'Class B' AS Part FROM dual UNION ALL
SELECT 1001 AS IDS, 'Class C' AS Part FROM dual UNION ALL
SELECT 2002 AS IDS, 'Class A' AS Part FROM dual UNION ALL
SELECT 2002 AS IDS, 'Class B' AS Part FROM dual UNION ALL
SELECT 2002 AS IDS, 'Class C' AS Part FROM dual UNION ALL
SELECT 3003 AS IDS, 'Class A' AS Part FROM dual UNION ALL
SELECT 3003 AS IDS, 'Class B' AS Part FROM dual),

cte_EmployeeCount AS
(
SELECT IDS, COUNT(*) AS LicenseCount
FROM Employees
GROUP BY IDS
),
cte_EmployeeCountCombined AS
(
SELECT a.IDS AS EmployeeID, b.IDS AS EmployeeID2, COUNT(*) AS LicenseCountCombo
FROM Employees a INNER JOIN
Employees b ON a.Part = b.Part
WHERE a.IDS <> b.IDS
GROUP BY a.IDS, b.IDS
)

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.IDS;