MySql: Get count of incremented items by multiple conditions

Question

Here is the dummy data, it's a calls record data table.

This is a glimpse of it:

|  call_id  |   customer   |   company   |     call_start      | 
|-----------|--------------|-------------|---------------------|
|1411482360 | 001143792042 | 08444599175 | 2014-07-31 13:55:03 |
|1476992122 | 001143792042 | 08441713191 | 2014-07-31 14:05:10 |

The customer and company fields represents their telephone numbers.

• The requirement is to compute the total 'gain' and total 'lost' values based on the following logic:
  

EDIT:

-Customer A calls Company A.
-If customer A calls Company B then Company B will have +1 gain and Company A will have +1 lost.
-If customer A calls Company C then Company C will have +1 gain and Company B will have +1 lost.
-If customer A calls Company C again then the spill/gain will not be affected.
-The gain/lost only comes into play once a 2nd call has been made by customer A.

- If a customer calls companies in this order: A, B, B, C, A, A, C, B, D the process should be like this:

A ->  
B ->  B +1 gain,  A +1 lost
B ->  
C ->  C +1 gain,  B +1 lost
A ->  A +1 gain,  C +1 lost
A ->  
C ->  C +1 gain,  A +1 lost
B ->  B +1 gain,  C +1 lost
D ->  D +1 gain,  B +1 lost

After above process we should have the total values as:

Company    Total gain    Total lost
  A            1             2            
  B            2             2       
  C            2             2         
  D            1             0     

I started working on this but it's wrong, it's just an ideea, it doesn't give me separate incremented gain and lost values based on the above conditions:

DROP TABLE IF EXISTS GetTotalGainAndLost;

CREATE TEMPORARY TABLE IF NOT EXISTS GetTotalGainAndLost
    AS 
        (
        SELECT SUM(count) as 'TotalGainAndLost', `date`, DAY(`date`) as 'DAY' 
        FROM (SELECT count(*) as 'count', customer, `date` 
            FROM (SELECT customer, company, count(*) AS 'count', DATE_FORMAT(`call_end`,'%Y-%m-%d') as 'date' 
                FROM calls 
                WHERE `call_end` LIKE CONCAT(2014, '-', RIGHT(CAST(concat('0', 01) AS CHAR),2),'-%')
                GROUP BY customer, company, DAY(`call_end`) ORDER BY `call_end` ASC)
            as tbl1 group by customer, `date` having count(*) > 1) 
        as tbl2 GROUP by `date`
        );

Select * from GetTotalGainAndLost;

DROP TABLE GetTotalGainAndLost;

This query doesn't show any results.

• The desired output would be something like below:
  

Should be one row per company and date (total gain and lost calls by day in e.g. january)

|  company    |  totalGain |  totalLost  |     date     |  DAY  | 
|-------------|------------|-------------|--------------|-------|
| 08444599175 |     17     |       6     | 2014-07-01   |  1    |
| 08444599175 |     12     |      10     | 2014-07-02   |  2    |
| 08444599175 |      3     |       6     | 2014-07-02   |  3    |
| 08444599175 |   ....     |      ...    |     ...      | ...   |
| 08444599175 |      7     |       6     | 2014-07-31   | 31    |

Answer 1

Simplification

Let denote N as the number times company has appeared. Lets try to simplify the formula in three simple rules.

1. The first company that appear will has N - 1 gains, N loss.
2. The mid company will has N gains, N loss.
3. The last company will has N gains, N - 1 loss

---

Testing

In your example:

• Starting with company A, and it appears 3 times.
• Company B appears 3 times
• Company C appears 2 times
• End with company D that appears 1 times.

Result

Company      Gain           Lost  
  A            2             3            
  B            3             3       
  C            2             2         
  D            1             0    

---

Translate to SQL

First we start by counting the number appearance of each company.

SELECT
    company, COUNT(*) AS gain, COUNT(*) AS lost, DATE(call_start) AS date
FROM calls 
GROUP BY DATE(call_start), company

Then, we start select the number that each company appear the first time for each customer.

SELECT company, -COUNT(*) AS gain, 0 AS lost, DATE(call_start) AS `date`
FROM calls INNER JOIN (
    SELECT MIN(call_id) AS call_id FROM calls GROUP BY DATE(call_start), customer
) AS t ON (calls.call_id = t.call_id)
GROUP BY DATE(call_start), calls.company

The number of company that appear last.

SELECT company, 0 AS gain, -COUNT(*) AS lost, DATE(call_start) AS `date`
FROM calls INNER JOIN (
    SELECT MAX (call_id) AS call_id FROM calls GROUP BY DATE(call_start), customer
) AS t ON (calls.call_id = t.call_id)
GROUP BY DATE(call_start), calls.company

---

Combine SQL

Finally, we can combine the whole SQL together using UNION ALL and then do another group by.

SELECT company, SUM(gain) AS gain, SUM(lost) AS lost, `date` FROM (
    (
        SELECT
            company, COUNT(*) AS gain, COUNT(*) AS lost, DATE(call_start) AS `date`
        FROM calls 
        GROUP BY DATE(call_start), company
    ) UNION ALL (
        SELECT company, -COUNT(*) AS gain, 0 AS lost, DATE(call_start) AS `date`
        FROM calls INNER JOIN (
            SELECT MIN(call_id) AS call_id FROM calls GROUP BY DATE(call_start), customer
        ) AS t ON (calls.call_id = t.call_id)
        GROUP BY DATE(call_start), calls.company
    ) UNION ALL (
        SELECT company, 0 AS gain, -COUNT(*) AS lost, DATE(call_start) AS `date`
        FROM calls INNER JOIN (
            SELECT MAX(call_id) AS call_id FROM calls GROUP BY DATE(call_start), customer
        ) AS t ON (calls.call_id = t.call_id)
        GROUP BY DATE(call_start), calls.company
    )
) AS t
GROUP BY `date`, company

---

Clarification

The above query make assumption that each new day is independence. For example,

• Customer A call Company A (Day 1)
• Customer A call Company B (Day 1) B gains 1, A lost 1
• Customer A call Company C (Day 1) C gains 1, B lost 1
• Customer A call Company D (Day 2)
• Customer A call Company E (Day 2) E gains 1, D lost 1

The result would be

COM   G     L   DAY
 ----------------
A     0     1    1
B     1     1    1
C     1     0    1
D     0     1    2
E     1     0    2

Answer 2

I think the easiest way to do this is with two queries. First we can get total gains counting every call made to a different company by each customer:

select g.company company, count(g.call_id) gain
from calls c
join calls g on c.customer = g.customer and c.company <> g.company and c.call_start < g.call_start
left join calls m on g.customer = m.customer and g.company <> m.company and g.call_start > m.call_start and m.call_start > c.call_start
where m.call_id is null
group by g.company;

Left join is needed to don't count extra gains if customer makes various calls to various companies (i. e. if customer calls in order to company a, b and c company c only has one gain, not two).

Total lost with the same approach:

select l.company company, count(l.call_id) lost
from calls c
join calls l on c.customer = l.customer and c.company <> l.company and c.call_start > l.call_start
left join calls m on l.customer = m.customer and l.company <> m.company and c.call_start > m.call_start and l.call_start < m.call_start
where m.call_id is null
group by l.company;

Here's a little fiddle demoing the solution: http://sqlfiddle.com/#!2/3236ab/7