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?