examples - MySQL AVG(TIMESTAMPDIFF) con GROUP BY

sql count group by

Question

Tengo dos tablas de user (one) y transaction (many) y necesito obtener el tiempo promedio en días desde que se creó un usuario hasta cuando hicieron su primera transacción. Estoy usando AVG(TIMESTAMPDIFF) que funciona bien, excepto que GROUP BY devuelve un promedio contra cada usuario en lugar de un promedio único para todos los usuarios únicos en la tabla de transaction . Si elimino GROUP BY , obtengo una cifra promedio única, pero tiene en cuenta las transacciones múltiples de los usuarios, mientras que solo quiero tener una por usuario (la primera que hicieron).

Aquí está mi SQL:

SELECT AVG(TIMESTAMPDIFF(DAY, u.date_created, t.transaction_date)) AS average
FROM transaction t
LEFT JOIN user u ON u.id = t.user_id
WHERE t.user_id IS NOT NULL AND t.status = 1
GROUP BY t.user_id;

Agradecería que alguien me ayude a devolver el promedio solo para usuarios únicos. Está bien dividir la consulta en dos, pero las tablas son grandes, por lo que devolver muchos datos y volver a colocarlos es un "no-go". Gracias por adelantado.

Answer 1

SELECT AVG(TIMESTAMPDIFF(DAY, S.date_created, S.transaction_date)) AS average
FROM (
SELECT u.date_created, t.transaction_date
FROM transaction t
INNER JOIN user u ON u.id = t.user_id
WHERE t.status = 1
GROUP BY t.user_id
HAVING u.date_created = MIN(u.date_created)
) s

Reemplacé el LEFT JOIN con un INNER JOIN porque creo que eso es lo que quieres, pero no es 100% equivalente a WHERE t.user_id IS NOT NULL .
Siéntase libre de volver a colocar la UNIÓN IZQUIERDA si es necesario.

Answer 2

select avg( TIMESTAMPDIFF(DAY, u.date_created, min_tdate) ) as average
from user u
inner join
(select t.user_id, min(t.transaction_date) as min_tdate
from transaction t
where t.status=1;
group by t.user_id
) as min_t
on u.id=min_t.user_id;