Avoding CT and other sins?

Question

I'm a SQL learner. In answering the question* For the region with the largest sales total_amt_usd, how many total orders were placed? for the following schema:

(I am unsure of the dialect is but I think it is Postgres)

I came up with the following solution and I wonder if am committing any bad practice (since the official solution is very different).

SELECT r.name AS region_name, COUNT(*) AS orders_n FROM ORDERS AS o INNER JOIN accounts AS a ON o.account_id = a.id INNER JOIN sales_reps as s ON a.sales_rep_id = s.id INNER JOIN region as r ON s.region_id = r.id GROUP BY 1 ORDER BY SUM(o.total_amt_usd) DESC LIMIT 1;

Official solution:

WITH t1 AS ( SELECT r.name region_name, SUM(o.total_amt_usd) total_amt FROM sales_reps s JOIN accounts a ON a.sales_rep_id = s.id JOIN orders o ON o.account_id = a.id JOIN region r ON r.id = s.region_id GROUP BY r.name), t2 AS ( SELECT MAX(total_amt) FROM t1) SELECT r.name, COUNT(o.total) total_orders FROM sales_reps s JOIN accounts a ON a.sales_rep_id = s.id JOIN orders o ON o.account_id = a.id JOIN region r ON r.id = s.region_id GROUP BY r.name HAVING SUM(o.total_amt_usd) = (SELECT * FROM t2);

_{* This is a part of exercise 4.13 of a great online course by Udacity.}

Diane M · Accepted Answer · 2021-01-27 13:40:30Z

Your solution honestly sounds great and better than proposed solution in readability, length, probably performance as well (although it's never too easy to know the engine complexity based on how they optimizes requests). And from my understanding there is no semantic difference.

Some nitpicks: I indent request differently, and I use the column name rather than index in the GROUP BY

SELECT r.name AS region_name, COUNT(*) AS orders_n FROM orders AS o INNER JOIN accounts AS a ON o.account_id = a.id INNER JOIN sales_reps AS s ON a.sales_rep_id = s.id INNER JOIN region AS r ON s.region_id = r.id GROUP BY region_name ORDER BY SUM(o.total_amt_usd) DESC LIMIT 1;

The request optimization engine will equally optimize your request and decide itself the join order if you use a WHERE clause. So you can also write it like this

SELECT r.name AS region_name, COUNT(*) AS orders_n FROM orders AS o, accounts AS a, sales_reps AS s, region AS r WHERE o.account_id = a.id AND a.sales_rep_id = s.id AND s.region_id = r.id GROUP BY region_name ORDER BY SUM(o.total_amt_usd) DESC LIMIT 1;

Stack Exchange Network

Avoding CT and other sins?

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Avoding CT and other sins?

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions