365 Days of Daily Challenge: Day 83

The best thing about today was that I woke up completely refreshed. This, I think has partly due to my pre-bed habits that I have been working on. I try to limit my exposure to smartphones and laptops and instead read. I am quite tired today but I am happy that I managed to solve two difficult challenge that will significantly improve my position in the leadership board. I wish I had taken it more seriously but I am happy with my position as is now.

Difficult Challenge 1

SELECT COUNT(customer_id) AS count
FROM(
SELECT DISTINCT customer_id 
FROM(
SELECT customer_id, date - lag AS delta
FROM(
SELECT *, lag(date) OVER(PARTITION BY customer_id ORDER BY date)
FROM(
SELECT customer_id, rental_id, DATE(rental_ts) AS date
FROM rental
WHERE DATE(rental_ts) between '2020-05-24' and '2020-05-31'
ORDER BY customer_id) AS k) AS l) AS m
WHERE delta = 1) AS n;

Difficult Challenge 2

WITH temp AS 
(
SELECT *, LAG(amt) OVER(PARTITION BY customer_id ORDER BY date),
ROW_NUMBER() OVER (PARTITION BY customer_id) AS rown
FROM(
SELECT customer_id, amount AS amt, payment_ts AS date
FROM payment
WHERE customer_id IN (1,2,3,4,5,6,7,8,9,10)) AS k)

SELECT customer_id, (amt - lag) AS delta FROM
(
SELECT *, LAST_VALUE(rown) OVER (PARTITION BY customer_id)
FROM temp) AS k
WHERE rown = last_value