Calculate conversion rate with the specified conditions-CodePudding

Here is my sample data table:

ID	Status
1	New
1	Processed
2	New
2	Processed
3	New
3	Processed
4	Processed
5	New

What I am trying to solve here is calculate the conversion rate from Status 'New' to Status 'Processed'. From the dataset, only ID no.1,2 and 3 fulfilled the requirements of my problem, and ID no.4 and 5 do not have both stages. So by theory, the conversion rate should be 3/5 * 100% = 60%. How can I select the data in order to calculate the IDs that have both 'New' and 'Processed' status.

This is the code that I have tried but I know its wrong since it extracts all the IDs with no link between it.

SELECT 'Conversion rate from Assigned enquiry to In progess leads' as 'Name', round((a.processed / b.new),3) * 100 as 'Answer'
FROM
(
SELECT cast(count(ID)as float) as processed
from table1
WHERE STATUS_ID = 'Processed'
) as a
cross join
(
SELECT cast(count(ID)as float) as new
from table_1
WHERE STATUS_ID = 'NEW'
) as b

CodePudding user response：

We can use conditional aggregation here:

WITH cte AS (
    SELECT CASE WHEN COUNT(CASE WHEN Status = 'New' THEN 1 END) > 0 AND
                     COUNT(CASE WHEN Status = 'Processed' THEN 1 END) > 0
                THEN 1 ELSE 0 END AS cnt
    FROM yourTable
    GROUP BY ID
)

SELECT 100.0 * SUM(cnt) / COUNT(*)
FROM cte;