MySQL: AVG of defined variable per month [migrated]
-- Calculate the previous order date for each order
CREATE TEMPORARY TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
UserID  INT NOT NULL,
OrderDate   DATETIME NOT NULL,
Year    YEAR NOT NULL,
Month   TINYINT NOT NULL,
PreviousOrderDate   DATETIME
)
INSERT INTO Orders (OrderID, UserID, OrderDate, Year, Month, PreviousOrderDate)
    SELECT
        O2.orderid,
        O2.userid,
        FROM_UNIXTIME(O2.date) AS OrderDate,
        YEAR(FROM_UNIXTIME(O2.date)) AS Year,
        MONTH(FROM_UNIXTIME(O2.date)) AS Month,
        MAX(O1.date_time) AS PreviousOrderDate
    FROM
        xcart_orders AS O2
        LEFT JOIN xcart_orders AS O1 ON O2.userid = O1.userid AND O1.date_time < O2.date_time
    GROUP BY
        O2.orderid,
        O2.userid,
        O2.date

-- Calculate the average for each year and month
SELECT
    Year, Month, COUNT(*) AS Orders, SUM(PreviousLogins) / COUNT(*) AS AvgPrevLogins
FROM
    (
    -- Get the number of previous logins for each order
    SELECT
        O.OrderID, O.Year, O.Month, COUNT(L.userid) AS PreviousLogins
    FROM
        Orders AS O
        LEFT JOIN xcart_login_history AS L
            ON  O.UserID = L.userid
            -- Filter logins here in the join rather than in a WHERE clause, or you exclude orders that HAVE no previous logins (in practice, such may not exist)
            AND L.date < O.OrderDate
            AND (L.date > O.PreviousOrderDate OR O.PreviousOrderDate IS NULL)  -- Only count logins from after the last previous order (if one exists)
            AND L.status = 'success'
            AND L.action IN ('login', 'autologin')
    GROUP BY
        O.OrderID, O.Year, O.Month
    ) AS X
GROUP BY
    Year, Month
ORDER BY
    Year, Month