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