Sales Analysis DQ- Shop using SQL Subquery

source from demodesk

In this case, “we can make a report for product management. The report is presentation data from loyal customers on every product. Loyal customers are customers with count transactions more than the average transaction in every product.”

Before we solve that case, we must understand subquery SQL.

Subquery is a query in query SQL.

The data requirements for the cross-selling program in DQ Shop would be easier to fulfill by utilizing subqueries. Subquery consists of two parts, there are Outer Query and Inner Query. We can use subqueries in the SELECT, FROM, WHERE, or JOIN clauses.

When using the subqueries, there are several rules that need to be consisted.

1. Subqueries must be enclosed in parentheses.
2. Subqueries can only have one column in the SELECT clause.
3. Subqueries that produce more than one value can be used in the IN operator.

There are several types of subqueries that are important to know:

1. Single Row Subquery will produce can only one result as an input. It is commonly used in the WHERE clause.
2. Multiple Row Subquery will produce more than one result as in input. It is commonly used for comparison operators such as IN, ANY, and ALL
3. Correlated Subquery, Unlike the types mentioned above, in a Correlated Subquery, the inner query is executed repeatedly based on criteria that match the outer query.

You can read the documentation of the subquery at this link https://dev.mysql.com/doc/refman/8.0/en/subqueries.html

Back to the case of Sales Analysis for DQ Shop, we are asked to create a report on the data of loyal customers’ presence.

We will subquery JOIN to solve the case, see the syntax below:

SELECT 
A.product, 
A.total_buyer, 
D.loyal_customer
FROM (
   SELECT product, COUNT(DISTINCT customer_id) total_buyer
   FROM data_retail
   GROUP BY 1) A
JOIN (
   SELECT B.Product, COUNT(DISTINCT Customer_ID) loyal_customer
   FROM data_retail B
   JOIN (
    SELECT Product, AVG(Count_Transaction) AS Count_Transaction
    FROM data_retail 
    GROUP BY 1
   ) C ON C.Product = B.Product AND B.Count_Transaction > C.Count_Transaction
   GROUP BY 1
   ) D ON A.Product = D.Product

and the resulting output is as follow

The code above is an SQL query that retrieves multiple data results from the “data_retail” table using several SELECT, JOIN, and GROUP BY commands.

In the first part of the subquery, section A:

SELECT product, COUNT(DISTINCT customer_id) total_buyer
   FROM data_retail
   GROUP BY 1

This subquery retrieves the “Product” column and counts customer_id using count distinct function. This data will be sorted based on the “Product” column, and the result will be stored in the subquery.

In the second part of the subquery, Section B:

SELECT B.Product, COUNT(DISTINCT Customer_ID) loyal_customer
FROM data_retail B
JOIN (
SELECT Product, AVG(Count_Transaction) AS Count_Transaction
FROM data_retail
GROUP BY 1
) C ON C.Product = B.Product AND B.Count_Transaction > C.Count_Transaction
GROUP BY 1

The subquery retrieves the “Product” column and counts customer_id using count distinct. This data will be sorted based on the “Product” column, and the result will be stored in the subquery, and the result will save in subquery D. This subquery uses JOIN to combine the results of subquery C with subquery B based on the “Product” column and the condition B.Count_Transaction > C.Count_Transaction.

In the last part of subquery, Let’s see the JOIN command between subquery A and subquery D:

JOIN (
SELECT B.Product, COUNT(DISTINCT Customer_ID) loyal_customer
FROM data_retail B
JOIN (
SELECT Product, AVG(Count_Transaction) AS Count_Transaction
FROM data_retail
GROUP BY 1
) C ON C.Product = B.Product AND B.Count_Transaction > C.Count_Transaction
GROUP BY 1
) D ON A.Product = D.Product

the JOIN command will combine the results of subquery A and subquery D based on the “Product” column.

Therefore, this query will result in the “Product” column from subquery A, the “total_buyer” column from subquery A, and the “total_customer” column from subquery D.

Thanks for reading, I hope it’s useful. suggestions and feedback will be very valuable so you can connect to LinkedIn to talk to much.