SQLPad
Instructions:
- Write a query to return the number of happy customers from May 24 (inclusive) to May 31 (inclusive).
Definition
- Happy customer: customers who made at least 1 rental in each day of any 2 consecutive days.
Hint
- For customer 1, you can create the following temporary table:
- customer 1, first rental date, second rental date
- customer 1, second rental date, third rental date ..............
- customer 1, second last rental date, last rental date
- customer 1, last rental date, NULL
- As long as there is at least one row, where the delta of the last 2 columns are not null, and less or equal than 1 day, this customer must be a happy customer.
Table: 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
count ------- 123 (1 row)
Expected results
Solution postgres
WITH customer_rental_date AS (
SELECT
customer_id,
DATE(rental_ts) AS rental_date
FROM rental
WHERE DATE(rental_ts) >= '2020-05-24'
AND DATE(rental_ts) <= '2020-05-31'
GROUP BY
customer_id,
DATE(rental_ts)
),
customer_rental_date_diff AS (
SELECT
customer_id,
rental_date AS current_rental_date,
LAG( rental_date, 1) OVER(PARTITION BY customer_id ORDER BY rental_date) AS prev_rental_date
FROM customer_rental_date
)
SELECT COUNT(*) FROM (
SELECT
customer_id,
MIN(current_rental_date - prev_rental_date)
FROM customer_rental_date_diff
GROUP BY customer_id
HAVING MIN(current_rental_date - prev_rental_date) = 1
) X
;
Explanation
This query retrieves the count of customers who rented a movie within a consecutive day period during the last week of May 2020.
The query does this by first creating a common table expression (CTE) called `customer_rental_date`. This CTE filters the `rental` table to only include rentals that occurred between May 24th and May 31st, 2020. It then groups the rentals by customer and rental date.
The second CTE called `customer_rental_date_diff` calculates the time difference between the current rental date and the previous rental date for each customer.
The final SELECT statement selects the count of customers who have rented movies on consecutive days. It does this by first selecting the customer ID and the minimum time difference between the current rental date and the previous rental date for each customer. It then groups the results by customer ID and filters the results to only include customers whose minimum time difference is equal to 1 day.