How can I create a column with the number of months since a user joined (first date for each id), and group all ids that joined on that month in SQL?

user_11792040 0
Viewed 32

Here's some more detail, since it's a bit hard to clearly ask this question in a sentence:

Basically, I have a table with some of the following fields:

| ID       | date                 | start_date            | amount_paid | last_amount_paid | field      |
| -------- | ---------------------| ----------------------| ----------- | ---------------- | ---------- |
| ID_00001 | 2020-08-01 00:00:00  | 2019-11-06 20:23:36   | 0           |  0               | cosmetics  |
| ID_00002 | 2020-08-02 00:00:00  | 2018-10-06 10:34:21   | 10          |  0               | finance    |
| ...      | ...                  | ...                   | ...         |  ...             | ...        |
| ID_99999 | 2021-11-06 00:00:00  | 2020-08-01 11:54:47   | 15          |  10              | software   |

What I want is to add a "months" column that counts the number of months between the start date and date for each ID, for example:

| ID       | date                 | start_date            | ...  | months     |
| -------- | ---------------------| ----------------------| ---- | ---------- |
| ID_00001 | 2020-08-01 00:00:00  | 2019-11-06 20:23:36   | ...  |  9         |
| ID_00002 | 2020-08-02 00:00:00  | 2018-10-06 10:34:21   | ...  |  22        |
| ...      | ...                  | ...                   | ...  |  ...       |
| ID_99999 | 2021-11-06 00:00:00  | 2020-08-01 11:54:47   | ...  |  15        |

I then want to group all IDs that have started (first start date) at the same time together (i.e. I want to group users by number of months).

I'm having a difficult time wrapping my mind around doing this using SnowflakeSQL.

The goal here is basically to track revenue by cohorts based on when they joined. Please let me know if my approach is wrong and how you would go about implementing that.

Much appreciated!

snowflake-cloud-data-platform sql
1 Answers

Using computed/generated column and DATEDIFF:

CREATE OR REPLACE TABLE t(id TEXT,
                          date DATE,
                          start_date DATE,
                          months INT AS  (DATEDIFF(MONTH, start_date, date))
                         );

Sample data:

INSERT INTO t(id, date, start_date)
SELECT  'ID_00001',  '2020-08-01 00:00:00', '2019-11-06 20:23:36'
UNION SELECT  'ID_00002', '2020-08-02 00:00:00', '2018-10-06 10:34:21';

SELECT * FROM t;

Output:

enter image description here

Alernatively wrapping table with a view:

CREATE VIEW t_vw
AS
SELECT t.id, t.start_date, t.date, DATEDIFF(MONTH, start_date, date) AS months
FROM t;
user_50708790
Related