Match data from multiple subqueries based on a shared property

Neo4j Graph Platform
1

Hello, I'm trying to make a query to select call data from different agents using two subqueries. From the returns of the subqueries, I'd like to separate how many total calls an agent has handled, and how many followup calls they handled.

My current results are accurate for total calls, but just "580" for the followup column. I've tried a few variations of my query without success.

My query:

// get phone calls that were followed up. 
CALL {
    MATCH(p:Person)-[r:HAD_CALL]->(e:PhoneCall)
    MATCH(p:Person)-[r2:HAD_CALL]->(e2:PhoneCall)
    where 
    e.name <> e2.name and
    e2.time > e.time
    return e.agent_name as required_followup_agent
    , count(e2.time) as followups order by e.agent_name asc
}

// get total phone calls. 
CALL {
    MATCH(p:Person)-[r:HAD_CALL]->(e:PhoneCall)
    return 
    e.agent_name as all_calls_agent
    , count(e) as totalcalls order by e.agent_name asc
} 

return all_calls_agent
,totalcalls as TotalInboundPhoneCalls
,followups as TotalInboundFollowUps

My current results:
image

How it should look:
image

Is there a way that I could achieve the desired result? Thank you!

2

I don’t understand the first subquery. Can you explain what defines a follow up call? Who is the person. A phone call has an agent?

3

The first subquery gets a list of calls where there was another phone call after it (at any point in time). "Person" = a customer calling in to the customer service line.

The "agent" is the customer service agent that handled the phone call.

4

So, any call for a given customer after the initial call from that customer is considered a 'followup'? Is so, are you just tallying the number of follow calls for each agent? This count would be the number of calls that agent received from a customer that was not the first call received by that customer?

5

Assuming what I stated is true, I came up with this query. See if it works for you:

match(p:Person)
call {
        with p
        match(p)-[:HAD_CALL]->(a:PhoneCall)
        with a
        order by a.time asc
        skip 1
        return 'followup' as callType, a.agent as agent, count(*) as callCount
    union
        with p
        match(p)-[:HAD_CALL]->(a:PhoneCall)
        return 'all' as callType, a.agent as agent, count(*) as callCount
}
return agent, 
sum(case when callType = 'all' then callCount else 0 end) as totalCalls,
sum(case when callType = 'followup' then callCount else 0 end) as followUpCalls
1 Like
6

Thank you so much! This is exactly what I was looking for!

1 Like