Haris Hasan Haris Hasan - 4 months ago 9
SQL Question

On average, how many times a user came before making first purchase?

id(pk) user_id(int) came_to_site(date_time) purchases(int)
1 1 27-8-2016:10:12:23 0
2 2 27-8-2016:10:20:23 0
3 1 28-8-2016:10:12:23 1
4 3 29-8-2016:10:12:23 0
5 4 29-8-2016:11:40:23 0
6 4 30-8-2016:10:12:23 0
7 4 30-8-2016:12:12:23 1
8 1 30-8-2016:12:30:23 1


I have this table, I want to know, on average, how many times a user came before making first purchase.


  • We can ignore user 2 and 3 because they never made a purchase.

  • User 1 came 2 times before making a purchase.

  • User 4 came 3 times before making a purchase.



So average would be (2 + 3)/2 = 2.5

Any idea how can I write such a query?

Answer
select avg(cnt)
from
(
    select user_id, 1 + count(*) as cnt
    from tablename t1
    where purchases = 0
      and exists (select 1 from tablename t2
                  where t2.user_id = t1.user_id
                    and t2.purchases = 1)
      and not exists (select 1 from tablename t3
                      where t3.user_id = t1.user_id
                        and t3.purchases = 1
                        and t3.came_to_site < t1.came_to_site)
    group by user_id
)

The sub-query counts each user_id that has made a purchase (EXISTS), but not before current row (NOT EXISTS).

At main level, do AVG() to get average number.

Perhaps, depending on dbms, you need to do avg(cnt * 1.0) to avoid integer result.

Comments