SQLPad Forum question: 20. Customer who spent the most

20. Customer who spent the most

Can Someone pls help with solution.

My approach:

select customer.first_name, customer.last_name from customer left join payment on customer.customer_id = payment.customer_id
order by amount desc limit 1

Can some1 pls point out what's wrong

Answers

Comment

Your approach:

SELECT customer.first_name, customer.last_name FROM customer LEFT JOIN payment ON customer.customer_id = payment.customer_id
ORDER BY amount desc limit 1;

Your query just joins two tables and returns customer who spent maximum for the entire period included in payment table.

Below is 3 steps to solve this question?

Fisrt you should join 'customers' and 'payment' tables to get customer names, amont, and payment date.

Second you should calculate sum of amount for each client and filter out by Feb-2020.

Third you display the customer name with maximum amount in your resulting table:

WITH payment_cte AS (

SELECT first_name, last_name, amount, payment_ts

FROM customer c

JOIN payment p

ON c.customer_id = p.customer_id

coolest_customer AS (

SELECT first_name, last_name, sum(amount)

FROM payment_cte

WHERE payment_ts >= '2020-02-01' AND payment_ts < '2020-03-01'

GROUP BY first_name, last_name

ORDER BY sum DESC

LIMIT 1

)

SELECT first_name, last_name

FROM coolest_customer;

Myradgeldi (2)

April 30, 2025, 7:20 a.m.