Best approach to display all the users who have more than 1 purchases in a month in SQL-CodePudding

I have two tables in an Oracle Database, one of which is all the purchases done by all the customers over many years (purchase_logs). It has a unique purchase_id that is paired with a customer_id.The other table contains the user info of all the customers. Both have a common key of customer_id.

I want to display the user info of customers who have more than 1 unique item (NOT the item quantity) purchased in any month (i.e if A customer bought 4 unique items in february 2020 they would be valid as well as someone who bought 2 items in june). I was wondering what should my correct approach be and also how to correct execute that approach.

The two approaches that I can see are

Approach 1

Count the overall number of purchases done by all customers, filter the ones that are greater than 1 and then check if they any of them were done within a month. Use this as a subquery in the where clause of the main query for retrieving the customer info for all the customer_id which match this condition.

This is what i've done so far,this retrieves the customer ids of all the customers who have more than 1 purchases in total. But I do not understand how to filter out all the purchases that did not occur in a single arbitrary month.

SELECT * FROM customer_details
WHERE customer_id IN (
SELECT cust_id from purchase_logs
group by  cust_id
having count(*) >= 2);

Approach 2

Create a temporary table to Count the number of monthly purchases of a specific user_id then find the MAX() of the whole table and check if that MAX value is bigger than 1 or not. Then if it is provide it as true for the main query's where clause for the customer_info.

Approach 2 feels like the more logical option but I cannot seem to understand how to write the proper subquery for it as the command MAX(COUNT(customer_id)) from purchase_logs does not seem to be a valid query.

This is the DDL diagram.

This is the Sample Data of Purchase_logs

Customer_info

and Item_info

and the expected output for this sample data would be

It is certainly possible that there is a simpler approach that I am not seeing right now. Would appreciate any suggestions and tips on this.

CodePudding user response：

I want to display the user info of customers who have more than 1 purchases in a single arbitrary month.

Just add a WHERE filter to your sub-query.

So assuming that you wanted the month of July 2021 and you had a purchase_date column (with a DATE or TIMESTAMP data type) in your purchase_logs table then you can use:

SELECT *
FROM   customer_details
WHERE  customer_id IN (
         SELECT cust_id
         FROM   purchase_logs
         WHERE  DATE '2021-07-01' <= purchase_date
         AND    purchase_date < DATE '2021-08-01'
         GROUP BY cust_id
         HAVING count(*) >= 2
       );

CodePudding user response：

One approach might be to try

with multiplepurchase as (
select customer_id,month(purchasedate),count(*) as order_count
from purchase_logs
group by customer_id,month(purchasedate)
having count(*)>=2)
select customer_id,username,usercategory
from mutiplepurchase a
left join userinfo b
on a.customer_id=b.customer_id

CodePudding user response：

You need this query:

SELECT DISTINCT cust_id 
FROM purchase_logs
GROUP BY cust_id, TO_CHAR(purchase_date, 'YYYY-MON')
HAVING COUNT(DISTINCT item_id) > 1;

to get all the cust_ids of the customers who have more than 1 unique item purchased in any month and you can use with the operator IN:

SELECT * 
FROM customer_details
WHERE customer_id IN (
  SELECT DISTINCT cust_id -- here DISTINCT may be removed as it does not make any difference when the result is used with IN
  FROM purchase_logs
  GROUP BY cust_id, TO_CHAR(purchase_date, 'YYYY-MON')
  HAVING COUNT(DISTINCT item_id) > 1
);

CodePudding user response：

Expanding on @MT0 answer:

SELECT *
FROM   customer_details CD
WHERE  exists (
         SELECT cust_id
         FROM   purchase_logs PL
         where CD.customer_id = PL.customer_id
         GROUP BY cust_id, item_id, to_char(purchase_date,'YYYYMM')
         HAVING count(*) >= 2
       );