SQL Basics: Top 10 customers by

题意

题解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