How to use a self join to get the COUNT of how many people on my platform have worked at another com-CodePudding

I have a table in the form:

id	comp	employment_year
1	ShoesCo	2000
1	FeetOrg	2006
1	SizeEight	2012
2	ShoesCo	2001
2	SizeEight	2004
2	FeetOrg	2007
3	SizeEight	2001
3	ShoesCo	2004
3	FeetOrg	2007

I want to count (get the total) number of people who worked at ShoesCo prior to (employment_date) working at SizeEight. The id is the uniqueid for each employee. I am thinking of self-join but have limited experience with SQL.

The answer should be 2 for this example.

CodePudding user response：

If the data have no duplicates by (id,comp) then

SELECT COUNT(DISTINCT id)
FROM table t1
JOIN table t2 USING (id)
WHERE t1.comp = 'ShoesCo'
  AND t2.comp = 'SizeEight'
  AND t1.employment_year < t2.employment_year