MySql query - How to select products that were returned before being purchased using sql only

One solution is to link the table to itself through the product code, and use the data_transaction to find the instances of error, i.e. situations that should not actually occur.

As is the case, for example:

• Return a product that was never purchased;
• Issue a product before the purchase order enters the system;
• Return the product twice without it being re-shipped in the meantime.

Here is just one example of how to identify the products for which the shipping has a pre-purchase date. Using this example, I think you can create the logic to identify the remaining error situations.

SELECT 'Expedicao antes da compra' AS SituacaoErro,
       t1.prod_id,
       t1.prod_nome,
       t1.movimento_tipo,
       t1.data_transaction AS DataDeExpedicao,
       t2.data_transaction AS DataDeCompra
  FROM tbl_tabela t1 -- Expedicao
 INNER JOIN tbl_tabela t2 -- Compra
    ON t2.prod_id = t1.prod_id
 WHERE t1.movimento_tipo = 'shipped'  
   AND t2.movimento_tipo = 'ordemCompra'
   AND t2.data_transaction > t1.data_transaction  -- A data da ordem de compra é posterior à data de compra.

There are several alternatives to get the same result (you can user EXISTS, GROUP BY along with HAVING, etc.). This is just an example.

- Update after new comment

Extrapolating the situation to 3 moves, you could add one more INNER JOIN or maybe do the following:

SELECT *
  FROM 
  (
     SELECT prod_id,
            MAX(CASE WHEN movimento_tipo = 'ordemCompra' THEN data_transaction END)) AS DataOrdemCompra, -- Assumindo que pode ser vendido apenas uma vez
            MIN(CASE WHEN movimento_tipo = 'shipped' THEN data_transaction END)) AS DataExpedicao,  -- Data da primeira expedicao
            CASE WHEN movimento_tipo = 'return' THEN data_transaction END) AS DataDevolucao -- Data da devolucao
       FROM tbl_tabela
      GROUP BY prod_id
  ) Sumario
 WHERE Sumario.DataOrdemCompra > Sumario.DataExpedicao
    OR Sumario.DataDevolucao > Sumario.DataOrdemCompra
    OR Sumario.DataDevolucao > Sumario.DataExpedicao