SQLPad
Instruction
- Write a query to return the first and last name of the customer who spent the most on movie rentals in Feb 2020.
Table 1: customer
col_name | col_type -------------+-------------------------- customer_id | integer store_id | smallint first_name | text last_name | text email | text address_id | smallint activebool | boolean create_date | date active | integer
Table 2: payment
Movie rental payment transactions table
col_name | col_type --------------+-------------------------- payment_id | integer customer_id | smallint staff_id | smallint rental_id | integer amount | numeric payment_ts | timestamp with time zone
Sample results
first_name | last_name -----------+----------- JAMES | BOND
Expected results
Solution postgres
WITH cust_feb_spend AS (
SELECT
customer_id,
SUM(amount) AS cust_amt
FROM payment
WHERE DATE(payment_ts) >= '2020-02-01'
AND DATE(payment_ts) <= '2020-02-29'
GROUP BY customer_id
ORDER BY cust_amt DESC
LIMIT 1
)
SELECT first_name, last_name
FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM cust_feb_spend
);
Explanation
This query extracts the first and last names of customers who made the highest total amount of payments during February 2020.
The query first creates a subquery named "cust_feb_spend" that calculates the total amount of payments made by each customer during the month of February 2020. This subquery groups the results by customer ID and orders them by the total amount spent in descending order, limiting the output to just the customer with the highest total amount spent.
The main query then selects the first and last names of all customers whose IDs match those found in the "cust_feb_spend" subquery.
In summary, the query identifies the customer with the highest spending during February 2020 and returns their first and last names.