SQL joins on phone_number where duration is less than 10min and returning only name column-CodePudding

I have 2 tables; phones and calls. and I am trying to write a SQL query that finds all clients who talked for at least 10 minutes in total. So the 'phone_number' in phones would match with the 'caller' and 'callee' column in calls and we can sum the durations to find which names have at least 10 minute calls.

phones

name    phone_number
..................
Jack    1234
Lena    3333                                     
Mark    9999                                       
Anna    7582

calls

id  caller  callee  duration
.............................
25  1234    7582    8
7   9999    7582    1
18  9999    3333    4 
2   7582    3333    3
3   3333    1234    1
21  3333    1234    1

The query should return only the name column alphabetically.

name  
.....
Anna 
Jack

I know I have to use some type of join and aggregate function here but not sure how to start.

Anyone have any ideas?

Thanks!!

CodePudding user response：

Join both tables on the caller column and group by name. Do the same for the callee column and UNION both results together. Then group by name and filter by SUM(duration).

SELECT name
FROM phones JOIN calls ON phones.phone_number = calls.caller
UNION 
SELECT name
FROM phones JOIN calls ON phones.phone_number = calls.callee
GROUP BY name
HAVING SUM(duration) > 10;

CodePudding user response：

Hi you can do it by using CTE (result here)

with call_duration as (
select caller as phone_number, sum(duration) as duration from calls group by caller
union all
select callee as phone_number, sum(duration) as duration from calls group by callee
)
SELECT name
FROM phones p join call_duration cd on cd.phone_number = p.phone_number
GROUP BY name
HAVING SUM(duration) >= 10
ORDER BY name