Analyzing Sales by Country

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/5/analyzing-sales-by-country

I have a few questions about this page:

  1. When we are asked to group countries with only one customer into a new group ‘other’, does that mean that we should create a running count of ALL ‘others’ and compile them into ONE category named ‘other’, or rather that each country with only one customer should be individually represented as ‘other’?

  2. Why is the created view (below) counting customer_id? What does this achieve? Does it count how many customer IDs a customer has? This page gives no explanation.

CREATE VIEW top_5_names AS
     SELECT
         first_name,
         count(customer_id) count
     FROM customer
     GROUP by 1
     ORDER by 2 DESC
     LIMIT 5;


SELECT * FROM top_5_names;
  1. The subquery (below) seems to ORDER BY ‘sort’ in its outer query, but the results that the page gives don’t seem to be sorted in ASC order. I also have no idea why ‘Mark’ is being used.
SELECT
    first_name,
    count
FROM
    (
    SELECT
        t5.*,
        CASE
            WHEN t5.first_name = "Mark" THEN 1
            ELSE 0
        END AS sort
    FROM top_5_names t5
   )
ORDER BY sort ASC
  1. ALSO, in the assignment, we are asked to include:
  • average value of sales per customer
  • average order value

What is the difference between these two? They seem to ask for the same thing.

ANY guidance would be VERY MUCH appreciated!

Hi @Willyjgolden! I am in the same step of this project.

I suppose DQ gives us an example of how to make an “other countries” group, with inner select, but there is no clue to analyze the result:)

According to the metrics, my opinion is
** average value of sales per customer*= total sum of sales/ total number of invoices by customer
** average order value* = just average amount of sales

Good luck!

Thank you @nskazenova! I just ignored one part of the question and continued with the project!