SQL 习题总结 - subqueries

Subquery 1

1. What was the month/year combo for the first order placed?

SELECT DATE_TRUNC('month', MIN(occurred_at)) 
FROM orders;

2. The average amount of standard paper sold on the first month that any order was placed in the orders table (in terms of quantity).
3. The average amount of gloss paper sold on the first month that any order was placed in the orders table (in terms of quantity).
4. The average amount of poster paper sold on the first month that any order was placed in the orders table (in terms of quantity).

SELECT AVG(standard_qty) avg_std, AVG(gloss_qty) avg_gls, AVG(poster_qty) avg_pst
FROM orders
WHERE DATE_TRUNC('month', occurred_at) = 
     (SELECT DATE_TRUNC('month', MIN(occurred_at)) FROM orders);

5. The total amount spent on all orders on the first month that any order was placed in the orders table (in terms of usd).

SELECT SUM(total_amt_usd)
FROM orders
WHERE DATE_TRUNC('month', occurred_at) = 
      (SELECT DATE_TRUNC('month', MIN(occurred_at)) FROM orders);

Subquery Mania

1. Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.

Step 1: rep_name; region_name; max_total_amt_usd

SELECT s.name rep_name, 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