SQL 习题总结 - subqueries

最新推荐文章于 2025-12-16 09:18:35 发布

原创

最新推荐文章于 2025-12-16 09:18:35 发布 · 297 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

本文总结了关于SQL子查询的应用，包括找出首次下单的月份年份组合，首次下单月的标准纸、高光纸和海报纸的平均销量，以及首次下单月的总消费金额。进一步探讨了如何找到每个区域销售代表中销售额最大的代表，以及针对最大销售额的区域，订单数量、账户购买量超过标准纸最多账户的次数，以及最高消费客户在其客户生涯中的网页事件数量。同时，分析了花费最多的前10个账户的平均终身消费，以及花费超过平均订单金额的公司平均消费。

Subquery 1

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

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

The average amount of standard paper sold on the first month that any order was placed in the orders table (in terms of quantity).
The average amount of gloss paper sold on the first month that any order was placed in the orders table (in terms of quantity).
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);

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

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 r