Q:

MYSQL MIN on Average of Inner Join

Given that there are two tables:

events 
users

Each user is allowed to have one or more events.
Each event can only be owned by one user.
I put together the following query to average the difference between the corresponding created_at timestamps of events and users.
SELECT 
(
    SELECT AVG(TIMESTAMPDIFF(DAY, users.created_at, events.created_at))
    FROM users
    INNER JOIN events
    ON users.id = events.user_id
    WHERE events.created_at IS NOT NULL
    AND users.created_at IS NOT NULL
) AS "Difference between created_at of user and created_at of user's first event, averaged across all users."

However, how would one go about only including the FIRST overlap, where the timestamp of the event.created_at is lowest? I believe at present, that the code would take the difference between each and every timestamp and the created_at, and average those out. What I am trying to accomplish to get the differences for every user between his/her first created event, do this for every user in the database, and average the results.
The solution is probably quite simple, but I was unable to come up with a working query. 
Any help or advice appreciated!

A:

I think you will need a subquery to find the earliest event so that you get one row in the event join...something like this?
SELECT AVG(TIMESTAMPDIFF(DAY, u.created_at, e.created_at))
FROM users u
INNER JOIN events e ON u.id = e.user_id
INNER JOIN
(
    SELECT user_id, MIN(created_at) minDate
    FROM events
    GROUP BY user_id
) m 
ON m.user_id = u.user_id AND e.created_at = m.minDate
WHERE e.created_at IS NOT NULL AND u.created_at IS NOT NULL

In case that's not clear, what I've done there is taken your original join giving all events for each user, but this is then joined against the earliest event for each user, so we just end up with one row per user who has at least one event.
This can be simplified as we don't need need that first join against events, we can just join users against that subquery...
SELECT AVG(TIMESTAMPDIFF(DAY, u.created_at, m.minDate))
FROM users u
INNER JOIN
(
    SELECT user_id, MIN(created_at) minDate
    FROM events
    GROUP BY user_id
) m 
ON m.user_id = u.user_id
WHERE u.created_at IS NOT NULL

