How to get the customer info of the customer with the highest number of transactions

Question

I have an oracle sql database consisting of three tables and I was wondering, What is the most efficient subquery that can be written to retrieve the information of the customer stored in the table customer_info who has performed the highest amount of purchases in total.(The purchase data is in the table purchase_logs). i.e the number of transactions one customer has performed NOT the quantity of the items purchased. i.e my aim is to retrieve the customer details of the customer witht he highest amount of purchases done. I have 3 tables one for the customer_info, one as the purchase_logs and the last one being the item_info.

My current Approach

SELECT * FROM customer_info
WHERE customer_id = (SELECT cust_id
    FROM purchase_logs 
    GROUP BY cust_id
    ORDER BY COUNT(*)
    DESC LIMIT 1);

This doesn't seem to give me any results at all unfortunately.

This is my Database Schema along with the Sample Data of purchase_logs, customer_info, item_info and the Expected Output

I would really appreciate any help in understanding what the proper approach to solving this problem would be.

Answer 1

There is no limit 1 in Oracle SQL, use row limiting clause instead (fetch first in the example below):

SELECT * 
FROM 
    (SELECT cust_id, count(*) cnt
     FROM purchase_logs 
     GROUP BY cust_id
     ORDER BY cnt desc
     fetch first 1 row only with ties
    ) vc
    join customer_info
        on customer_id = vc.cust_id;