So I have an SQL Microsoft Acces Problem. It asks me to look at a table full of

Question

So I have an SQL Microsoft Acces Problem. It asks me to look at a table full of DATA with two different categories. The customers ID number, the product the costumer bought.

The costumer can only buy Product a Product b or product c. If the costumer buys one of those products and it fulfills it need he does not need to go back to buy one of the other two to fulfill his need. but with some customers they have had to go throu gh all 3 of the products until they landed the righ one.

The table gives me purchases of each customer how can I program a query to tell me the percentage of costumers who came back after their first try to purchas one of the two left products.

So for this data the percent of costumers that returned top the store to purchase their second product because the first one did not work is 66% as customer number three only had to purchase product B and it fulfilled his/hers needs

Explanation / Answer

We will need to split the query into sub-queries which can count returning customers & also get distinct customer count.

The first sub-query part for getting the count of returning customers -

SELECT COUNT(returning_customer) AS return_customer FROM (SELECT Customer_Id AS returning_customer, COUNT(Customer_Id) AS visits FROM Product_Purchase GROUP BY Customer_Id HAVING COUNT(Customer_Id) > 1)

The second sub-query part for getting the count of distinct customer list -

SELECT COUNT(Customer_Id) AS all_customer FROM (SELECT DISTINCT Customer_Id FROM Product_Purchase)

The final query which returns the percentage of customers that returned again to the store -

SELECT qry1.return_customer * 100 / qry2.all_customer FROM (SELECT COUNT(returning_customer) AS return_customer FROM (SELECT Customer_Id AS returning_customer, COUNT(Customer_Id) AS visits FROM Product_Purchase GROUP BY Customer_Id HAVING COUNT(Customer_Id) > 1)) AS qry1, (SELECT COUNT(Customer_Id) AS all_customer FROM (SELECT DISTINCT Customer_Id FROM Product_Purchase)) AS qry2

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.