WITH pure AS (
SELECT a.candidate_id, a.grp
FROM assignments a
WHERE a.round = 2
AND NOT EXISTS (
SELECT 1 FROM assignments b
WHERE b.candidate_id = a.candidate_id AND b.round = a.round AND b.grp <> a.grp)
)
SELECT p.grp,
COUNT(*) AS leads,
SUM(EXISTS (SELECT 1 FROM events e WHERE e.candidate_id = p.candidate_id AND e.round = 2 AND e.stage = 'contacted')) AS contacted,
SUM(EXISTS (SELECT 1 FROM events e WHERE e.candidate_id = p.candidate_id AND e.round = 2 AND e.stage = 'replied')) AS replied,
SUM(EXISTS (SELECT 1 FROM events e WHERE e.candidate_id = p.candidate_id AND e.round = 2 AND e.stage = 'interview')) AS interview,
SUM(EXISTS (SELECT 1 FROM events e WHERE e.candidate_id = p.candidate_id AND e.round = 2 AND e.stage = 'offer')) AS offer
FROM pure p
GROUP BY p.grp
ORDER BY p.grp
-- EXPLAIN QUERY PLAN(建索引后)
SCAN a
CORRELATED SCALAR SUBQUERY 1
SEARCH b USING COVERING INDEX idx_assign (candidate_id=? AND round=?)
USE TEMP B-TREE FOR GROUP BY
CORRELATED SCALAR SUBQUERY 3
SEARCH e USING COVERING INDEX idx_events (candidate_id=? AND round=? AND stage=?)
CORRELATED SCALAR SUBQUERY 4
SEARCH e USING COVERING INDEX idx_events (candidate_id=? AND round=? AND stage=?)
CORRELATED SCALAR SUBQUERY 5
SEARCH e USING COVERING INDEX idx_events (candidate_id=? AND round=? AND stage=?)
CORRELATED SCALAR SUBQUERY 6
SEARCH e USING COVERING INDEX idx_events (candidate_id=? AND round=? AND stage=?)