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 21
Customer who rented the most
Instruction
- Write a query to return the first and last name of the customer who made the most rental transactions in May 2020.
Table 2: rental
col_name | col_type --------------+-------------------------- rental_id | integer rental_ts | timestamp with time zone inventory_id | integer customer_id | smallint return_ts | timestamp with time zone staff_id | smallint
Sample results
first_name | last_name ------------+----------- JENNIFER | ANISTON
Solution
postgresWITH cust_may_rentals AS (
SELECT
customer_id,
COUNT(*) AS cust_rentals
FROM rental
WHERE DATE(rental_ts) >= '2020-05-01'
AND DATE(rental_ts) <= '2020-05-31'
GROUP BY customer_id
ORDER BY cust_rentals DESC
LIMIT 1
)
SELECT first_name, last_name
FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM cust_may_rentals
);Explanation
This query retrieves the first and last names of the customer who rented the most movies in May 2020.
First, it creates a temporary table (CTE) called cust_may_rentals, which counts the number of rentals for each customer in May 2020 and orders the result in descending order by the number of rentals. It then selects the customer with the most rentals by using the LIMIT 1 clause.
Next, the main query selects the first and last names of the customer(s) whose customer ID matches the customer ID(s) in the cust_may_rentals table. It does this by using a subquery in the WHERE clause that selects the customer ID(s) from the cust_may_rentals table.
Overall, this query allows a data analyst to quickly identify the customer who rented the most movies in May 2020 and retrieve their first and last names for further analysis or reporting.
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.