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



More LAG, LEAD questions

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%
180 Members moved from Microsoft to Google directly. linkedin hard
21%
147 2 days streak customers google hard
13%
121 Week over week change of first ever bookings airbnb hard
14%
114 Dod revenue change rate amazon easy
15%
80 DoD revenue growth for each store hard
19%
79 Spend difference between the last and the second last rentals hard
16%
68 Spend difference between first and second rentals hard
12%