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

Question

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

Answer 1

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.