A way to generate a subscription id and group transactions per subscription per user

subscription_id.sql

WITH aggregated_subs AS (
SELECT
    USER_ID
    ,PRODUCT_ID
    ,TRANSACTION_START
    ,TRANSACTION_DATE_DUE
    ,ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID ORDER BY TRANSACTION_START) AS rn
    ,LAG(TRANSACTION_DATE_DUE, 1) OVER (PARTITION BY PRODUCT_ID ORDER BY TRANSACTION_DATE_DUE) AS previous_sub_end
FROM SUBSCRIPTIONS
-- ORDER BY TRANSACTION_START, rn
)
, subscription_series AS (
    SELECT *
         , DATEDIFF(DAY, previous_sub_end, TRANSACTION_START) AS diff
         , IFF(diff > 0 OR diff is null, 1, 0) AS subscription_serie_start_idx
         , SUM(subscription_serie_start_idx) OVER (ORDER BY USER_ID, rn) AS subscription_serie_id
    FROM aggregated_subs
)
SELECT
USER_ID
,PRODUCT_ID
,MIN(TRANSACTION_START) AS sub_series_start
,MAX(TRANSACTION_DATE_DUE) AS sub_series_end
,COUNT(subscription_serie_id) AS num_payments
,DATEDIFF(MONTH, sub_series_start, sub_series_end) AS sub_series_length_in_months
FROM subscription_series
GROUP BY USER_ID, PRODUCT_ID
ORDER BY USER_ID ASC, PRODUCT_ID ASC, sub_series_start ASC