SQLPad
Instruction
- Write a query to return the first and last name of the customer who made the most rental transactions in May 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: 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
Expected results
Solution postgres
WITH 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.