1
\$\begingroup\$

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:

enter image description here

(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.

\$\endgroup\$
1
\$\begingroup\$

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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.