Q122

Hi All,

For this one, I got a slightly different approach, yet I found there are ties in both Aug 3rd and 4th. Any way I can bypass this? Code: 

WITH rnk_for_each_listing_aug AS
(SELECT date, booking_id, listing_id, 'first_week_of_aug' AS week,
               ROW_NUMBER()OVER(PARTITION BY listing_id ORDER BY date) rnk1
FROM bookings
WHERE date BETWEEN '2021-08-01' AND '2021-08-07'
),
rnk_for_each_listing_1week_before_aug AS
(SELECT date, booking_id, listing_id, 'last_week_before_aug' AS week,
               ROW_NUMBER()OVER(PARTITION BY listing_id ORDER BY date) rnk2
FROM bookings
WHERE date BETWEEN '2021-07-25' AND '2021-07-31' 
),

book_on_each_day AS
(SELECT country,date, COUNT(DISTINCT first_rent.listing_id) as first_booking_on_that_day
FROM listings l
JOIN (SELECT * FROM 
      rnk_for_each_listing_aug
      WHERE rnk1 = 1
      UNION ALL
      SELECT * FROM 
      rnk_for_each_listing_1week_before_aug
      WHERE rnk2 = 1) first_rent
ON l.listing_id = first_rent.listing_id
GROUP BY 1,2)

SELECT date, country
FROM
(SELECT *, RANK()OVER(PARTITION BY date ORDER BY wow DESC) as ranks
FROM
(SELECT country, date, ((first_booking_on_that_day - LAG(first_booking_on_that_day,7)OVER(ORDER BY date)) * 100.00 / LAG(first_booking_on_that_day,7)OVER(ORDER BY date) ) as wow
FROM book_on_each_day) sub
) tmp
WHERE date BETWEEN '2021-08-01' AND '2021-08-07' AND ranks =1 

Also, the date >= DATE('2022-08-01') -  integer '7'  Or DATE('2022-08-01') -  interval '7' day function doesn't work as intended.

Best

Ming