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
Question 20
Customer who spent the most
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 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
Solution
postgresWITH 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.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Your results
Run your query to preview results here.