time series based on months since account opening rather than actual time-CodePudding

I don't expect a full answer on this, just some pointers on how to tackle, as I haven't been able to search for what I want to do. My data is as follows with two tables

Table A (only one record for each account)

Account_id creation_date
0x66756    12-05-21
Ox77734    06-07-21
...        ...

Table B (multiple records for each account)

Account_id transaction_id transaction_value transaction_date
0x66756    AA344556       5.24              14-05-21
0x66756    AA556655       7.00              17-05-21
Ox77734    AA149876       9.66              08-07-21
0x66756    AA765900       1.63              25-06-21
Ox77734    AA433331       9.99              28-08-21

What I want is something to show this

account_id  count_of_transactions_month1_post_opening 
0x66756     2
Ox77734     1

Ideally then build so I can show a complete time series of how many transactions in every month post opening, but will be easier to start with this then iterate

CodePudding user response：

A LEFT JOIN and a ON Condition with the date, give you the correct count

Thsi will select the same Month

SELECT A.[Account_id], COUNT( B.[Account_id]) as count_of_transactions_month1_post_opening 
FROM TableA A LEFT JOIN TableB B  ON A.[Account_id] = B.[Account_id]
AND FORMAT([transaction_date],'YYYY-MM') 
= FORMAT([creation_date],'YYYY-MM')
GROUP BY A.[Account_id]

Thsi will select exact one Month after created date from tableA

SELECT A.[Account_id], COUNT( B.[Account_id]) as count_of_transactions_month1_post_opening 
FROM TableA A LEFT JOIN TableB B  ON A.[Account_id] = B.[Account_id]
AND [transaction_date]
BETWEEN  [creation_date] AND DATEADD(month, 1,[creation_date])
GROUP BY A.[Account_id]

The result would be in both cases(i added a account for 0

CodePudding user response：

You can use tableB only and aggregate on account_id and month, Extract month from the transaction date field and use it in the group by clause. I assume that account is able to transact only when it is active Else you can join with TableA on account id and the filter on transaction date>= create date

In final select you can keep only the required columns.