Cohort Cumulative Rates in SQL

  • I have a question regarding a problem for cohort analysis in SQL (any dialect).

    I have two tables:

    • users

      • user_id VARCHAR(40)
      • first_seen TIMESTAMP

    • subscriptions

      • user_id VARCHAR(40)
      • event_time TIMESTAMP
      • subscription_product_id VARCHAR(40)

    I want to write a query that aggregates user_ids on weekly cohorts based on first_seen and computes the cumulative subscriptions at a given offset time:

    • D1 (same day)
    • D30 (a month after)
    • D90
    • D360

    I.e. D90 for a given weekly cohort is the fraction of the users from that cohort that subscribed within the 90 days.

    The issue here is that a user (as in the subscription table) can purchase multiple subscription_product_id, and in that case I want only to consider their first purchase.

    An example of the outcome would be:​

    Screen Shot 2022-01-26 at 9.01.25 PM

    And so on.

    I imagine that the way to approach the problem, once the two tables are joined,

    is to use a window function to calculate the cumulative value at different o

    offsets, but I can't figure out exactly how to set the levels.

    Any hint is highly appreciated.

    Thanks!

  • If you only care about users' first subscriptions, can you assign a number of days until first subscription to every user?

    Then assign each user an offset code (D1, D30, D90, D360, DInfinity). No subscription = DInfiity. Then group by the calendar week of the first seen date and count the subscribers by offset.

    You can then calculate a running total of subscriptions partitioned by week, ordered by offset code (or an offset order column if needed). Once you have the running total, convert it to a percentage based on the total number of users first seen during that week.

    Finally remove the DInfinity offset rows from the output. They need to be included initially so that your final percentage is not 100%.

    Does that sound like it what you need?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply