Codewars Kata SQL
SQL Basics: Top 10 customers by total payments amount
For this kata we will be using the DVD Rental database.
Your are working for a company that wants to reward its top 10 customers with a free gift. You have been asked to generate a simple report that returns the top 10 customers by total amount spent. Total number of payments has also been requested.
The query should output the following columns:
- customer_id [int4]
- email [varchar]
- payments_count [int]
- total_amount [float]
and has the following requirements:
- only returns the 10 top customers, ordered by total amount spent
DVD Rental ER Model
Hint: total_amount requires data type “float”. “cast(field name as float)” can convert data types.
select c.customer_id as customer_id, c.email as email, count(*) as payments_count, sum(p.amount) as total_amount from customer c join
payment p on
c.customer_id = p.customer_id
GROUP BY customer_id, email
ORDER BY total_amount;