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?

Improve this question
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. Commented Jul 13, 2012 at 17:23
  • I'm not clear on exactly what you need. Can you illustrate with some sample data and desired output? Commented Jul 13, 2012 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. Commented Jul 17, 2012 at 13:00
  • 1
    Huh, is the policy now to move any SQL-related questions to DBA if they're not answered on SO? Commented Jul 17, 2012 at 19:02

1 Answer 1

Reset to default
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
Improve this answer
1
  • Comments are not for extended discussion; this conversation has been moved to chat. Commented Aug 29, 2017 at 9:15

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.