Alexx_K Alexx_K - 6 months ago 30
SQL Question

How can I select customers who have certain number of orders and other conditions?

I am studying SQL. There is one particular query which I find difficult to implement (using PostgreSQL database).

Could someone help me with that?

Query : "Who are the customers who have at least one order which includes products from at least 3 product categories?".

The result should include 2 columns:

customerID
and
Quantity of orders
.

The tables are the following:


  1. Product
    ProductID (id of a product)
    |
    ProductCategoryID (product categories)
    both integers.

  2. SalesOrderDetail :
    SalesOrderID (id order)
    |
    SalesOrderDetailID
    |
    OrderQty
    |
    ProductID
    , all of them integers .

  3. SalesOrderHeader:
    SalesOrderID
    |
    CustomerID
    , both integers.



The only thing I got so far is the first part of the statement:

select salesorderheader.customerID, salesorderdetail.orderqty
from salesorderheader, salesorderdetail;


Sample data:

productid | productcategoryid
-----------+-------------------
1 | 2
2 | 2
3 | 3
4 |

salesorderid | salesorderdetailid | orderqty | productid
--------------+--------------------+----------+-----------
43659 | 1 | 1 | 776
43659 | 2 | 3 | 777
43659 | 3 | 1 | 778


salesorderid | customerid

--------------+------------
43659 | 29825
43660 | 29672
43661 | 29734

Answer

I see two approaches here:

1) Generate a subquery which contains the count a salesOrderID and the count of Distinct ProductCategories used for that salesorder.

SELECT customerID, count(Distinct OH.SalesOrderID) SalesOrdersWithMoreThan3Categories
FROM SalesOrderHeader OH
INNER JOIN (SELECT OD.SalesOrderID, Count(Distinct P.ProductCategoryID) Cnt
            FROM SalesOrderDetail OD 
            INNER JOIN Product P
              on P.ProudctID = OD.ProductID
            GROUP BY OD.SalesOrderID) B
   on OH.SalesOrderID = B.SaleOrderID
  and B.cnt >=3
GROUP BY  OH.customerID

2) Use Exists to identify orderDetails a count of distinct productCategories >=3

SELECT OH.customerID, count(Distinct OH.SalesOrderID) SalesOrdersWithMoreThan3Categories
FROM SalesOrderHeader OH
WHERE exists (SELECT 1
            FROM SalesOrderDetail OD 
            INNER JOIN Product P
              on P.ProudctID = OD.ProductID
            WHERE OH.SalesOrderID = OD.SalesOrderID
            GROUP BY OD.SalesOrderID
            HAVING Count(Distinct P.ProductCategoryID) >=3)
GROUP BY  OH.customerID