Skip to content

Instantly share code, notes, and snippets.

@ks--ks

ks--ks/subscription_id.sql

Created August 10, 2022 05:22
Show Gist options
  • Save ks--ks/d3562b9e48e83d0cfda9a0fc9a3b3218 to your computer and use it in GitHub Desktop.
Save ks--ks/d3562b9e48e83d0cfda9a0fc9a3b3218 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ks--ks created this gist Aug 10, 2022.
    28 changes: 28 additions & 0 deletions subscription_id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,28 @@
    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