SELECT 
    CONCAT(b.nome,' (',c.nome,')') as'status',
    d.nome 'proprietario',
    COUNT(a.id) qtde
FROM leads a
JOIN fases_pipeline b ON a.status = b.id
JOIN pipeline c ON c.id = b.id_pipeline
JOIN usuario d ON d.id = a.proprietario
WHERE 
a.data_criacao BETWEEN '' AND ''
GROUP BY b.nome, d.nome;



SELECT 
    b.nome as 'vendedor',
    COUNT(a.id) AS quantidade_vendas,
    SUM(a.valor) AS valor_total,
    AVG(a.valor) AS ticket_medio
FROM venda a
JOIN usuario b ON a.id_usuario = b.id
WHERE 
a.data_criacao BETWEEN '' AND ''
GROUP BY b.id;



SELECT 
    b.nome as 'vendedor',
    DATE_FORMAT(a.data_criacao, '%m/%Y') AS mes,
    COUNT(a.id) AS quantidade_vendas,
    SUM(a.valor) AS valor_total
FROM venda a
JOIN usuario b ON a.id_usuario = b.id
WHERE 
a.data_criacao BETWEEN '' AND ''
GROUP BY b.nome, DATE_FORMAT(a.data_criacao, '%m/%Y');


SELECT 
    CONCAT(b.nome,' (',c.nome, ')') AS fase_status,
    a.data_entrada,
    a.data_saida
FROM data_etapa a
JOIN fases_pipeline b ON a.id_etapa = b.id
JOIN pipeline c ON c.id = b.id_pipeline;



SELECT 
    CONCAT(b.nome, ' (', c.nome, ')') AS fase_status,
    TIME_FORMAT(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, a.data_entrada, a.data_saida))), '%H:%i:%s') AS tempo_medio
FROM data_etapa a
JOIN fases_pipeline b ON a.id_etapa = b.id
JOIN pipeline c ON c.id = b.id_pipeline
JOIN leads d ON d.id = a.id_objeto
WHERE a.data_saida IS NOT NULL
GROUP BY b.nome, c.nome;