Untitled

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