CODEWITHSUNDEEP
mysqlcount
shoaib sipai
shoaib sipai

Reputation: 157

SQL query to get the number of customers who purchased on multiple dates

I'm having trouble coming up with a query to get the number of customers who purchased on multiple dates.

We're given a table of product purchases. Each row in the table represents an individual user product purchase.if the customer purchased two things on the same day that does not count as an upsell as they were purchased within a similar timeframe.

'transactions' table:

column type
id integer
user_id integer
created_at datetime
product_id integer
quantity integer

I tried in this way

select count(*) 
  from 
         ( select user_id
                , count(date) 
             from 
                ( SELECT user_id
                      , DATE(created_at) AS date
                   FROM transactions
                 GROUP BY 1,2
                 ) S
              group
                  by 1
             having count(date)>1
          ) A

Upvotes: 0

Views: 12898

Answers (2)

dmg
dmg

Reputation: 4491

Count the distinct dates per user, first, then count from table when the count is > 1.

See below:

WITH T as (select user_id, 
           count(distinct DATE(created_at)) as count 
           from  transactions 
           GROUP BY user_id) 
  select count(*) from T where count > 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

I think you want:

SELECT COUNT(*)
FROM
(
    SELECT user_id
    FROM transactions
    GROUP BY user_id
    HAVING COUNT(DISTINCT DATE(created_at)) > 1
) t;

The subquery finds all users having transacted on more than one date, the outer query finds the count of such users.

Upvotes: 4

Related Questions