Gross sales of top ten customers that made at least 5 purchases of some Category in a specified year

user_12481716 0
Viewed 35

I need to get (sales.customers) | year | gross_sales of top ten customers that made at least 5 purchases of Category "Beverages" in the year 2014. I have already written these SELECT queries, but since I am new to SQL, I think I am very inefficient in writing code. This does not work properly and there is probably a simpler way of doing it. I have also pinned a picture of an ER diagram .

SELECT 
    T6.COMPANYNAME, YEAR, GROSS_SALES 
FROM
    (SELECT T1.CUSTID 
     FROM
         (SELECT R.CUSTID, COUNT(R.CUSTID) AS NUMBEROFSALES 
          FROM SALES.ORDERDETAILS O
          RIGHT JOIN PRODUCTION.PRODUCTS P ON P.PRODUCTID = O.PRODUCTID 
          RIGHT JOIN SALES.ORDERS R ON R.ORDERID = O.ORDERID
          INNER JOIN SALES.CUSTOMERS C1 ON R.CUSTID = C1.CUSTID
          INNER JOIN PRODUCTION.CATEGORIES C2 ON P.CATEGORYID = C2.CATEGORYID
          WHERE C2.CATEGORYNAME = 'Beverages' AND YEAR(R.ORDERDATE) = 2014
          GROUP BY R.CUSTID
          ORDER BY SUM(R.CUSTID) DESC) T1
          --HAVING COUNT(R.CUSTID) > 5
     RIGHT JOIN
         (SELECT R.CUSTID, SUM(O.UNITPRICE) AS MONEYSPENT 
          FROM SALES.ORDERDETAILS O
          RIGHT JOIN PRODUCTION.PRODUCTS P ON P.PRODUCTID = O.PRODUCTID 
          RIGHT JOIN SALES.ORDERS R ON R.ORDERID = O.ORDERID
          INNER JOIN SALES.CUSTOMERS C1 ON R.CUSTID = C1.CUSTID
          INNER JOIN PRODUCTION.CATEGORIES C2 ON P.CATEGORYID = C2.CATEGORYID
          WHERE C2.CATEGORYNAME = 'Beverages' AND YEAR(R.ORDERDATE) = 2014
          GROUP BY R.CUSTID
          ORDER BY SUM(O.UNITPRICE) DESC) T2 ON T1.CUSTID = T2.CUSTID
     ORDER BY T1.NUMBEROFSALES DESC
     LIMIT 10) T5
INNER JOIN 
    (SELECT DISTINCT(T4.COMPANYNAME), T4.CUSTID, YEAR, GROSS_SALES 
     FROM 
         (SELECT R.CUSTID AS CUSTID, YEAR(R.ORDERDATE) AS YEAR, SUM(O.UNITPRICE * O.QTY * (1 - O.DISCOUNT)) AS GROSS_SALES 
          FROM SALES.ORDERDETAILS O
          RIGHT JOIN SALES.ORDERS R ON R.ORDERID = O.ORDERID
          INNER JOIN SALES.CUSTOMERS C1 ON R.CUSTID = C1.CUSTID
          GROUP BY R.CUSTID, YEAR(R.ORDERDATE)
          ORDER BY YEAR(R.ORDERDATE)) T3
     INNER JOIN
         (SELECT C.COMPANYNAME, C.CUSTID 
          FROM SALES.ORDERS R
          INNER JOIN SALES.CUSTOMERS C ON R.CUSTID = C.CUSTID) T4 ON T3.CUSTID = T4.CUSTID) T6 ON T5.CUSTID = T6.CUSTID

enter image description here

sql
1 Answers

I'm not going to try and fix your code or explain the misakes there, as there are many of them. Instead based on your requirements I wrote a query that solves the problem. I show it in steps below which should make clear the process I used to solve the problem.

First how do we find the top ten customers that made 5 purchases of Beverages?

Take customer table and join to orders with beverages (inner join will exclude customers that don't meet criteria)

SELECT CUSTOMERID
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMERID = O.CUSTOMERID
JOIN ORDER_DETAILS OD ON O.ORDERID = OD.ORDERID
JOIN PRODUCTS P ON OD.PRODUCTID = P.PRODUCTID
JOIN CATEGORIES C ON P.CATEGORYID = C.CATEGORYID AND C. CATEGORY_NAME = 'Beverages'
WHERE YEAR(ORDER_DATE) = 2014
GROUP BY CUSTOMERID
HAVING COUNT(ORDER_DETAILS) >= 5

Now we need the sum of order (for 2014) by customers which looks like this:

SELECT CUSTOMERID, YEAR(ORDER_DATE) AS YEAR, SUM(OD.UNIT_PRICE*OD.QUANTITY) AS TOTAL_SPEND
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMERID = O.CUSTOMERID
JOIN ORDER_DETAILS OD ON O.ORDERID = OD.ORDERID
WHERE YEAR(ORDER_DATE) = 2014
GROUP BY CUSTOMERID, YEAR(ORDER_DATE)

Now we just combine these two queries like this:

SELECT CUSTOMERID, YEAR(ORDER_DATE) AS YEAR, SUM(OD.UNIT_PRICE*OD.QUANTITY) AS TOTAL_SPEND
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMERID = O.CUSTOMERID
JOIN ORDER_DETAILS OD ON O.ORDERID = OD.ORDERID
JOIN (
    SELECT CUSTOMERID
    FROM CUSTOMERS C
    JOIN ORDERS O ON C.CUSTOMERID = O.CUSTOMERID
    JOIN ORDER_DETAILS OD ON O.ORDERID = OD.ORDERID
    JOIN PRODUCTS P ON OD.PRODUCTID = P.PRODUCTID
    JOIN CATEGORIES C ON P.CATEGORYID = C.CATEGORYID AND C. CATEGORY_NAME = 'Beverages'
    WHERE YEAR(ORDER_DATE) = 2014
    GROUP BY CUSTOMERID
    HAVING COUNT(ORDER_DETAILS) >= 5
) as SUB ON SUB.CUSTOMERID = C.CUSTOMERID
WHERE YEAR(ORDER_DATE) = 2014
GROUP BY CUSTOMERID, YEAR(ORDER_DATE)
ORDER BY SUM(OD.UNIT_PRICE*OD.QUANTITY) 
LIMIT 10

Note I did not test this but just wrote the SQL since I don't have a db to test against so there might be typos

Also Note: I'm expect it is possible to remove the sub query as it is doing a lot of the same joins the outer query is-- but we want to make sure we get the correct result and it is easier to see it is correct this way. You can also test the sub-query by itself to make sure it returns expected results.

user_2157520
Related