SQL实现：统计同时拥有入职与晋升里程碑记录的用户数

Hey there! Let's work through how to count the number of users who have both the Hired (milestone_id=1) and Promoted (milestone_id=2) records in your progress table. Below are a few reliable, standard SQL methods that work across most databases (MySQL, PostgreSQL, SQL Server, etc.):

Method 1: GROUP BY + HAVING Clause #

This is a straightforward and efficient approach for most scenarios. We group records by user, then filter to only keep groups that include both milestones we care about.

SELECT COUNT(DISTINCT user_id) AS qualified_users
FROM progress
WHERE milestone_id IN (1, 2)
GROUP BY user_id
HAVING COUNT(DISTINCT milestone_id) = 2;

Breakdown: #

• The WHERE clause narrows our focus to just the two milestones we need, reducing the data we process.
• GROUP BY user_id clusters all records per individual user.
• HAVING COUNT(DISTINCT milestone_id) = 2 ensures the user has both distinct milestones (since we're only looking at 1 and 2, this confirms they have both Hired and Promoted entries).
• Finally, COUNT(DISTINCT user_id) gives us the total number of users meeting the criteria.

Method 2: INNER JOIN #

We can join the table to itself to match users who have both milestones.

SELECT COUNT(DISTINCT p1.user_id) AS qualified_users
FROM progress p1
INNER JOIN progress p2 
    ON p1.user_id = p2.user_id
WHERE p1.milestone_id = 1 
  AND p2.milestone_id = 2;

Breakdown: #

• p1 represents the subset of users who were hired (milestone_id=1), and p2 represents those who were promoted (milestone_id=2).
• The INNER JOIN only retains users who exist in both subsets—meaning they have both milestones.
• COUNT(DISTINCT user_id) prevents duplicate counts if a user has multiple entries for the same milestone.

Method 3: EXISTS Subquery #

This approach is great if you want to avoid joins or grouping, and it’s often highly performant when you have indexes on user_id and milestone_id.

SELECT COUNT(DISTINCT user_id) AS qualified_users
FROM progress p1
WHERE p1.milestone_id = 1
  AND EXISTS (
      SELECT 1 
      FROM progress p2 
      WHERE p2.user_id = p1.user_id 
        AND p2.milestone_id = 2
  );

Breakdown: #

• We start with users who have the Hired milestone.
• The EXISTS clause checks if the same user has a Promoted milestone in the table (it stops searching as soon as it finds a match, making it efficient).

Which Method Should You Pick? #

• GROUP BY + HAVING: Ideal if you might need to extend the query later (e.g., add checks for more milestones like "Moved to New Office").
• INNER JOIN: Simple to read and understand, works well for small to medium-sized datasets.
• EXISTS: Often the fastest choice for large datasets, especially with proper indexing on (user_id, milestone_id).

All these methods will give you the correct count of users who’ve both been hired and promoted.

内容的提问来源于stack exchange，提问作者karhu