Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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? –  Jon of All Trades 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. –  user1524040 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? –  Jon of All Trades Jul 17 '12 at 19:02
add comment

migrated from stackoverflow.com Jul 16 '12 at 2:13

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
    
Hi Jon, thanks a lot for your input. This seems like what I was trying to do. However, I receive an error referring to the CAST(PreviousLogins AS REAL) statement. Now I am running the Query without the statement. It's been taking about an hour, but I guess with 7 million columns that is no suprise. –  user1524040 Jul 18 '12 at 12:07
    
You need to convert the count of previous visits to a floating-point number or you'll get integer division: 3 / 2 = 1. I don't have an installation of MySQL handy, but if it doesn't like the CAST(X AS REAL) construct, you can instead add 0.0. I'll edit the answer. –  Jon of All Trades Jul 18 '12 at 14:29
    
Ick, it looks like MySQL does implicit promotion to FLOAT when dividing integers. No wonder the language is commonly paired with PHP. IMHO, it's a bad plan for a tool to do non-standard "helpful" things; consistency is more important. But in your case it means that neither CAST() nor + 0.0 is necessary. –  Jon of All Trades Jul 18 '12 at 14:37
    
Ok, but that's perfect for my intentions. I am creating a subset of the login_history table since I think my machine is not capable of running the query on such a large table. I will keep you posted if it works. Thanks a lot for your support so far! –  user1524040 Jul 18 '12 at 15:20
    
@JonofAllTrades: But is this "automatic" integral division an altogether standard thing to do? I was under impression that SQL Server was the only product to automatically switch to integral division when both operands are integers (in SQL). –  Andriy M Jul 18 '12 at 15:20
show 19 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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