(I have no idea how to title this question)

I have two tables: leads and calls

leads consists of unique lead_id values for each lead and the associated phone_number for that lead. There can be many leads with the same phone number.

calls consists of lead_id values for each call to a lead and the associated status of each call. There is no phone_number field in the calls table.

I have the following query that will extract all phone numbers that do not have any calls in teh calls table (regardless of lead_id) that resulted in the DEAD status:

SELECT DISTINCT phone_number
FROM leads l
WHERE NOT EXISTS (
    SELECT 1
    FROM calls c
    WHERE c.lead_id IN (
        SELECT lead_id
        FROM leads
        WHERE phone_number = l.phone_number
    )
    AND c.status = "DEAD"
 );

Now, I am trying to limit the results to only phone numbers that have at least 6 different lead_id values in the calls table.

Any ideas?

CodePudding user response：

Join with the calls table, then use COUNT(DISTINCT c.lead_id) >= 6.

SELECT l.phone_number
FROM leads AS l
JOIN calls AS c ON l.lead_id = c.lead_id
WHERE c.status = "DEAD"
GROUP BY l.phone_number
HAVING COUNT(DISTINCT c.lead_id) >= 6

CodePudding user response：

SELECT l.phone_number
FROM (
    select phone_number, count(*), lead_id from leads 
    group by lead_id having count(*)>=6
) AS leads_may_have_6
  JOIN leads l on l.phone_number = leads_may_have_6.phone_number
where 
  not exists 
(select 1 from calls c on c.lead_id =l.lead_id where c.status='DEAD' limit 1)

In this example, leads_may_have_6 will be executed for O(N), all other are O(N^2). So shrinking leads to only numbers which already have 6 leads will greatly decrease execution time(I bet most time it will give zero results and will not execute another part at all).

But it is still questionable why you need leads which at least one times have DEAD status doesn't matter what are other attempts.

Select with limit 1 will fetch exactly one row even if you have few thousands attempts for that lead id. Since it select 1, it will just check index and not fetch row at all if you have index on lead_id,status.

CodePudding user response：

Here's my understanding from the questions and comments:

Requirements

• I am trying to get phone numbers that did not result in a "DEAD" status across 6 campaigns (denoted by the 6 lead_ids)
• I am trying to limit the results to only phone numbers that have at least 6 different lead_id values in the calls table.
• I have replaced all of the "final statuses" with the "DEAD" status for simplicity.
• In reality, I am looking for all the numbers that we have not contacted (any sort of contact would have resulted in a final, or DEAD, status).

Definitions

• Dead Lead: a lead_id with an entry in calls table where status='DEAD'
• Dead Phone Number: a phone number related to 6 or more dead leads (lead_id)

Request

• Give me all the phone numbers that are not Dead Phone Number

SQL Query

with cte_dead_leads as (
select lead_id 
  from calls 
 where status = 'DEAD')
 group by lead_id),
cte_dead_phone_number as (
select l.phone_number
  from leads l
  join cte_dead_leads d
 using (lead_id)
 group by l.phone_number
having count(*) >= 6)
select l.phone_number
  from leads l
  left
  join cte_dead_phone_number p
 using (phone_number)
 where p.phone_number is null
 group by l.phone_number;