CODEWITHSUNDEEP
mysqlsql
Ali Salavati
Ali Salavati

Reputation: 108

SQL - Get count of phone calls that resulted in meeting

I have a table named 'reports', something like this:

id user_id type customer_id text created_at
1 1 1 3 .... 2021-08-07 17:00:52
2 1 1 3 .... 2021-08-12 10:11:11
3 1 1 9 .... 2021-08-12 10:08:14
4 1 2 3 .... 2021-08-12 10:04:08
5 1 2 9 .... 2021-08-13 20:32:21
6 2 1 7 .... 2021-08-13 20:34:17
7 2 2 8 .... 2021-08-14 18:55:09

I want to get the count of rows that a user has submitted type 1 reports that result in a type 2 report.

Type 1 report means reporting a phone call to the customer and type 2 means meeting the customer. I want to receive the number of calls that resulted in an meeting.

For example, for user 1 should returns 3, because for customer 3, IDs 1 and 2 have led to ID 4, and for customer 9, ID 3 has led to ID 5.

But for user 2, ID 7 is type 2 but there is no previous type 1 report for customer 8, so it returns 0.

Upvotes: 1

Views: 241

Answers (3)

user16425306
user16425306

Reputation:

Schema (MySQL v5.7)

CREATE TABLE reports
    (id int auto_increment primary key,
     user_id int, 
     type int, 
     customer_id int, 
     text varchar(4), 
     created_at varchar(19)
    );
    
INSERT INTO reports
    (id, user_id, type, customer_id, text, created_at)
VALUES
    (1, 1, 1, 3, '....', '2021-08-07 17:00:52'),
    (2, 1, 1, 3, '....', '2021-08-12 10:11:11'),
    (3, 1, 1, 9, '....', '2021-08-12 10:08:14'),
    (4, 1, 2, 3, '....', '2021-08-12 10:04:08'),
    (5, 1, 2, 9, '....', '2021-08-13 20:32:21'),
    (6, 2, 1, 7, '....', '2021-08-13 20:34:17'),
    (7, 2, 2, 8, '....', '2021-08-14 18:55:09');

Query #1

SELECT x.user_id
     , COUNT(DISTINCT y.id) total
  FROM reports x
LEFT
JOIN reports y 
  ON y.id<=x.id
 AND y.user_id = x.user_id
 AND y.customer_id = x.customer_id
 AND y.type = 1
WHERE x.type = 2
GROUP
   BY x.user_id;
user_id total
1 3
2 0

View on DB Fiddle

Upvotes: 2

Stu
Stu

Reputation: 32629

You haven't shown how your are expecting results, but simply refer to User 1 and 2 and the total votes, you can try using a lateral join

select user_id, Coalesce(Sum(valid),0) rowcount
from reports r
join lateral (
    select 
    case when exists (select * from reports rr 
            where rr.user_id=r.user_Id and rr.type=2 and rr.customer_id=r.customer_Id and r.type=1)
    then 1 end valid
)x
group by User_Id

Example Fiddle

Upvotes: 1

nbk
nbk

Reputation: 49395

First you Count the number of customers that comply with your criterioa type and typoe 2. But ti get all user you need to join the list of users

I used LEFT join as also RIGHt JOIN in my example

CREATE TABLE reports
    (`id` int, `user_id` int, `type` int, `customer_id` int, `text` varchar(4), `created_at` varchar(19))
;
    
INSERT INTO reports
    (`id`, `user_id`, `type`, `customer_id`, `text`, `created_at`)
VALUES
    (1, 1, 1, 3, '....', '2021-08-07 17:00:52'),
    (2, 1, 1, 3, '....', '2021-08-12 10:11:11'),
    (3, 1, 1, 9, '....', '2021-08-12 10:08:14'),
    (4, 1, 2, 3, '....', '2021-08-12 10:04:08'),
    (5, 1, 2, 9, '....', '2021-08-13 20:32:21'),
    (6, 2, 1, 7, '....', '2021-08-13 20:34:17'),
    (7, 2, 2, 8, '....', '2021-08-14 18:55:09')
;

SELECT IFNULL(Counts,0),t1.user_id
FROM (SELECT COUNT(distinct r.customer_id) counts,user_id
FROM reports r 
WHERE type = 1 AND EXISTS(SELECT 1 FROM reports WHERE type = 2 AND customer_id = r.customer_id)
GROUP BY user_id) r RIGHT JOIN (SELECT DISTINCT user_id FROm reports) t1 ON r.user_id = t1.user_id

IFNULL(Counts,0) | user_id
---------------: | ------:
               2 |       1
               0 |       2

SELECT IFNULL(Counts,0),t1.user_id
FROM (SELECT DISTINCT user_id FROm reports) t1 LEFT JOIN (SELECT COUNT(distinct r.customer_id) counts,user_id
FROM reports r 
WHERE type = 1 AND EXISTS(SELECT 1 FROM reports WHERE type = 2 AND customer_id = r.customer_id)
GROUP BY user_id) r  ON r.user_id = t1.user_id

IFNULL(Counts,0) | user_id
---------------: | ------:
               2 |       1
               0 |       2

db<>fiddle here

Upvotes: 0

Related Questions