3

I need help with the following code:

SELECT DISTINCT 
    userid
    , count( userid ) as login_count
FROM 
    (
        SELECT DISTINCT 
            userid
            , date(FROM_UNIXTIME(date_time)) AS DAY
        FROM xcart_login_history
        WHERE status="success" 
            and (action ="login" or action = "autologin") 
        ORDER BY userid, DAY
    ) as login_days
WHERE login_days.DAY < 
    (
        SELECT DISTINCT min(date(FROM_UNIXTIME(xcart_orders.date)))
        FROM xcart_orders
        WHERE xcart_orders.userid = login_days.userid
        GROUP BY userid
    )
GROUP BY userid;

This shows me the COUNT of logins for individual users before purchasing. It is on purpose not returning more than one per day counted.

I now need the average login number / login count before purchase on a monthly basis.

For instance, the calculation should analyze the buys in January and calculate the average logins (max 1 per day) that were necessary for this purchase.

Could please somebody help me?

4
  • 1
    @Jean-Rémy As this seems more focused on the SQL language and less on database design, management, administration, etc, I would think this is the better place.
    – Jodaka
    Jul 13 '12 at 17:23
  • I'm not clear on exactly what you need. Can you illustrate with some sample data and desired output? Jul 13 '12 at 18:34
  • Thank you guys for your help. Let me try to explain: I want to find the average number of logins a customer has executed before actually buying something. Here an example of a row in the xcart_login_history: userid=1 ; date_time=1304006969; action=login; status=success Here an example of a row in the xcart_orders: userid=1 ; date=1329855906; These are actually all of the relevant information (dates are all UNIXTIME). What I want to see as an endresult: Month=January ; AVG_logins_before_buy= 17 Month=February ; AVG_logins_before_buy= 14 and so on. Jul 17 '12 at 13:00
  • 1
    Huh, is the policy now to move any SQL-related questions to DBA if they're not answered on SO? Jul 17 '12 at 19:02
2

Rewritten to only count logins after the last previous order:

-- 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
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29 '17 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.