Date_Daily_Island_with_Rank example

Date_Daily_Island_with_Rank.md

# Example with Dense Rank!
WITH RANKED_DATA AS (
SELECT 
  FullDate
  ,RegistrationGUID
  ,RegistrationNo
  ,PolicyNo
  ,Cover_YN
  ,DENSE_RANK () OVER (
    PARTITION BY 
      RegistrationNo
      ,Cover_YN
    ORDER BY FullDate ASC) 
  AS DateRank
FROM DimCustomer_Car_Status_by_day
)
SELECT 
  FullDate
  ,RegistrationGUID
  ,RegistrationNo
  ,PolicyNo
  ,Cover_YN
  ,DateRank
  --Now get the partition start date by substracting the Dense Rank - 1
  ,DATE(FullDate, '-' || (DateRank - 1) || ' DAYS') AS PartitionStartDate
FROM RANKED_DATA
;