How to filter properly in sql to achieve the desired query results?-CodePudding

What is the best way to return only the clients that have ever reached the closed stage and calculate its duration from new to closed? The first step I've been doing is to filter only who has closed deals but it returns all the clients that meet the criteria. Here's the SQL query I've tried:

SELECT
*

FROM
client_stages

WHERE
new_stage IN ("new","introduced","connected","met_in_person","closed")

Here is the sample table that I used to query:

The desired result should be:

CodePudding user response：

You'll need two subqueries.

The first finds the oldest entry for each client_id where old_stage is 'new'.

                       SELECT client_id, 
                              MIN(created_at) created_at 
                         FROM client_stages
                        WHERE old_stage = 'new'
                        GROUP BY client_id

Similarly, the second one finds the newest entry where the new stage is 'closed'.

                       SELECT client_id, 
                              MAX(created_at) created_at 
                         FROM client_stages
                        WHERE new_stage = 'closed'
                        GROUP BY client_id

Then you LEFT JOIN the first to the second in your main query. (EDIT added DATEDIFF() for duration).

SELECT a.client_id, 
       a.created_at when_new,
       b.created_at when_closed,
       DATEDIFF(b.created_at, a.created_at) duration
  FROM (
                       SELECT client_id, 
                              MIN(created_at) created_at 
                         FROM client_stages
                        WHERE old_stage = 'new'
                        GROUP BY client_id
       ) a
  JOIN (
                       SELECT client_id, 
                              MAX(created_at) created_at 
                         FROM client_stages
                        WHERE new_stage = 'closed'
                        GROUP BY client_id
       ) b ON a.client_id = b.client_id

This is based on a guess about how to handle duplicated 'new' or 'closed' entries.

CodePudding user response：

Try this,

SELECT c1.client_id AS clientId, MAX(CASE WHEN c1.old_stage='new' THEN c1.created_at ELSE NULL END) AS newDate, MAX(CASE WHEN c1.new_stage='closed' THEN c1.created_at ELSE NULL END) AS closedDate, DATEDIFF(MAX(CASE WHEN c1.old_stage='new' THEN c1.created_at ELSE NULL END), MAX(CASE WHEN c1.new_stage='closed' THEN c1.created_at ELSE NULL END)) duration FROM client_stages c1 WHERE c1.client_id IN (SELECT c.client_id FROM client_stages c WHERE c.new_stage IN ('closed')) GROUP BY c1.client_id;