# 69. Number of happy customers

hard

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)

```

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.

Last Submission postgres

Expected results

ID Title Level FTPR
230 Customer Purchase Trend Analysis microsoft easy -
229 Time Between Repeat Purchases microsoft hard -
199 Students improvement snap hard
67%
195 MoM user growth snap hard
14%