Transitioning from MS Access query designer to SQL

SQL is one of the most popular technologies that is used today for working with data. Of course, one can use visual tools, but SQL becomes important when one has to deal with large ammoutns of structured data to answer increasingly more complex questions.

I have started feeling that MS Access with its query designer that I have been using for decades is not sufficient for the work I am doing anymore. Therefore, using the extra time that the current uncontrolled Corona virus pandemic is providing, I have been learning some SQL. I have just obtained a certificate on SQL for data analysis course that I finished at Dataquest.

I must say I feel rather proud being able to write the query below for one of the training excercises.

WITH spc AS (
    SELECT invoice.customer_id,
            customer.country AS country,
            AVG(invoice.total) AS spc
    FROM invoice
    INNER JOIN customer ON invoice.customer_id = customer.customer_id
    GROUP BY invoice.customer_id
    ),
other AS (
        SELECT
        customer.country AS country,
        CASE
            WHEN COUNT(DISTINCT customer.customer_id) = 1 THEN 0
            ELSE 1
        END AS sort
    FROM customer
    GROUP BY customer.country
    )

SELECT 
    CASE
        WHEN other.sort = 0 THEN "Other"
        ELSE other.country
    END AS c,
    COUNT(DISTINCT customer.customer_id) AS customers,
    ROUND(SUM(invoice.total), 2) AS sales,
    ROUND(AVG(spc.spc), 2) AS avg_spc,
    ROUND(AVG(invoice.total), 2) AS avg_order
FROM customer
INNER JOIN invoice ON customer.customer_id = invoice.customer_id
INNER JOIN spc ON customer.country = spc.country
INNER JOIN other ON customer.country = other.country
GROUP BY c
ORDER BY other.sort DESC, sales DESC