Skip to content

Instantly share code, notes, and snippets.

@savadev

savadev/subscription_id.sql

Forked from ks--ks/subscription_id.sql
Created October 25, 2024 04:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save savadev/8cc85f158c3d51883a67f7535304adef to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save savadev/8cc85f158c3d51883a67f7535304adef to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ks--ks 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