Compare evolution of clients between more than two month on a table

Question

I have a database in which new records are entered on a monthly basis where I can see the number of clients and the name of the clients that each provider has. There are approximately 1500 clients and 17 suppliers. Elder clients can be disabled or new clients can be signed up each month. Each customer can only have a single provider but each month they can change providers. I am trying to perform a query that returns Who changed provider each month (client's name, who they were with, and with whom they're).

Everything in my query is working well but I want it to compare all months, not just the two months that I write on table 1 and table 2.

I devoloped the following code:

WITH
-- CREATE TABLE 1 FROM A GENERAL DATABASE WITH CURRENT MONTH INFO
t1 AS (
  SELECT 
        Transaction_date,
        Provider,
        Customer_ID,
        Customer_Name,
        CONCAT(Customer_ID, Provider) AS ID1,
        Demand
    FROM MyDataBase
    WHERE YEAR(Transaction_date)=2020
    AND MONTH(Transaction_date)=10
), 

-- CREATE TABLE 2 FROM A GENERAL DATABASE WITH LAST MONTH INFO
t2 AS (
  SELECT 
        Transaction_date AS Transaction_date2,
        Provider AS Provider2,
        Customer_Code AS Customer_ID2,
        Customer_Name AS Customer_Name2,
        CONCAT(Customer_ID, Provider) AS ID2,
        Demand AS Demand2
    FROM MyDataBase
    WHERE YEAR(Transaction_date)=2020
    AND MONTH(Transaction_date)=09
    
),


-- t3: IDENTIFY WHO CHANGED

t3 AS (
    SELECT 
        Transaction_date2,
        Provider2,
        Customer_ID2,
        Customer_Name2,
        Demand2
    FROM t1
    RIGHT JOIN t2
    ON t2.ID2 = t1.ID1
    WHERE demand IS null
),

-- t4: RETURNS THE CUSTOMERS THAT CHANGED OF PRIVIDER IN AN ORGANIZED TABLE

T4 AS (
    SELECT 
        Transacion_date,
        Provider2 AS 'Last_Provider',   
        Provider AS 'Actual_Privder',
        Customer_ID,
        Customer_Name,
        Demand
    FROM t3
        LEFT JOIN t1
        ON t1.Customer_ID = t5.Customer_ID2
        WHERE Demand IS NOT NULL 
        AND Transacion_date IS NOT NULL
    )
    

SELECT * FROM t4

Sample of Database

 Month        & Provider & Client & Demand \\
 2020-01-01   & A        & 1      & 50     \\
 2020-01-01   & A        & 2      & 40     \\
 2020-01-01   & B        & 3      & 55     \\
 2020-01-01   & B        & 4      & 70     \\
 2020-02-01   & A        & 1      & 55     \\
 2020-02-01   & B        & 2      & 34     \\
 2020-02-01   & B        & 3      & 67     \\
 2020-02-01   & B        & 4      & 89     \\
 2020-03-01   & A        & 1      & 78     \\
 2020-03-01   & A        & 2      & 89     \\
 2020-03-01   & A        & 3      & 76     \\
 2020-03-01   & B        & 4      & 45    

As you can see in the example

January: there were 4 clients. February: customer # 2 switched to supplier B. March: Customer # 2 and # 3 were moved to Provider A.

Answer 1

I am trying to perform a query that returns Who changed provider each month (client's name, who they were with, and with whom they're).

If you are running MySQL 8.0, you can use lag() to get the "previous" provider of each customer. Then all that is left to do is filter on months where the provider changed:

select *
from (
    select t.*,
        lag(provider) over(partition by customer_code order by transaction_date) as previous_provider
    from mytable t
) t
where provider <> previous_provider

In earlier versions, you can emulate the window function with a subquery:

select *
from (
    select t.*,
        (
            select t1.provider 
            from mytable t1 
            where t1.customer_code = t.customer_code and t1.transaction_date < t.transaction_date 
            order by t1.transaction_date desc limit 1
        ) as previous_provider
    from mytable t
) t
where provider <> previous_provider