| * | * |
|---|---|
| 链接 | SQL Basics: Top 10 customers by total payments amount |
| 难度 | 6kyu |
| 状态 | √ |
| 日期 | 2019-4-4 |
题意
题解1
select customer.customer_id, customer.email,
count(payment.customer_id) as payments_count,
cast(sum(payment.amount) as float) as total_amount
from customer
left join payment
on customer.customer_id = payment.customer_id
group by customer.customer_id
order by total_amount desc
limit 10
题解2，LZY
select C.customer_id, C.email,
count(*) as payments_count,
cast(sum(P.amount) as float) as total_amount
from customer as C, payment as P
where C.customer_id = P.customer_id
group by C.customer_id
order by total_amount desc
limit 10
拆解如下：
首先，参与的表只需要customer表（提供客户基本信息）及payment表（提供支付记录）。两张表合并（根据客户ID来join），可以得到payment表中的每笔支付记录对应到哪个客户。
select C.customer_id, C.email
from customer as C, payment as P
where C.customer_id = P.customer_id
接下来，需要汇总，统计每个客户有多少笔支付（count）以及总共花了多少（sum）。payment表中的amount字段表示了一笔交易记录的金额。汇总操作使用group by，字段显然是客户ID。
select C.customer_id, C.email
count(*) as payments_count,
cast(sum(P.amount) as float) as total_amount
from customer as C, payment as P
where C.customer_id = P.customer_id
group by C.customer_id
cast操作只是转换类型，numerical转换为float（题目要求）。
接下来，就简单了，只需要挑选消费最高的10个客户。根据消费金额倒序排下（order by操作），然后限制选择10个就行（limit操作）。
select C.customer_id, C.email,
count(*) as payments_count,
cast(sum(P.amount) as float) as total_amount
from customer as C, payment as P
where C.customer_id = P.customer_id
group by C.customer_id
order by total_amount desc
limit 10
