Buscar Clientes de Recompra

Tarefa 2171.sql

SELECT
  pxa2.storeno As Loja, pxa2.eordno AS Pedido, DATE_FORMAT(pxa2.date, '%d/%m/%Y') AS 'Data Compra',
  id_pessoa as 'Cod. Cliente', nome_pessoa as Cliente, REPLACE(ROUND(pxa2.amt/100, 2), '.', ',') as Valor
FROM (
       SELECT *
       FROM webpdv.pessoas pe
       WHERE data_cadastro BETWEEN '2015-01-01 00:00:00' AND '2016-03-18 23:59:59'
             AND EXISTS(SELECT 'X'
                        FROM sqlpdv.pxa
                        WHERE pxa.custno = pe.id_pessoa
                        AND date = date_format(pe.data_cadastro, '%Y%m%d')
             )
     ) t1
  INNER JOIN sqlpdv.pxa pxa2 ON (pxa2.custno = t1.id_pessoa)
WHERE pxa2.custno = t1.custno AND date > date_format(t1.data_cadastro, '%Y%m%d')
      AND amt >= 90000
ORDER BY t1.id_pessoa, pxa2.date
;