Create a table (QVD) with basic monthly user statistics

[RoyBatty]

Hi,

There is the following table:

[payments]:
Load * Inline
[
ID, USER_ID, AMOUNT, CREATED_AT
1, 1, 20, 2023-01-30 06:38:36
2, 2, 15, 2023-01-30 07:17:17
3, 1, 25, 2023-01-31 08:18:18
4, 3, 10, 2023-01-31 09:19:19
5, 3, 20, 2023-01-31 10:10:10
6, 1, 15, 2023-01-31 11:11:11

7, 1, 20, 2023-02-25 06:38:36
8, 2, 15, 2023-02-25 07:17:17
9, 1, 25, 2023-02-26 08:18:18
10, 1, 15, 2023-02-27 11:11:11

11, 2, 15, 2023-03-28 07:17:17
12, 3, 10, 2023-03-29 09:19:19
13, 3, 20, 2023-03-31 10:10:10
14, 3, 25, 2023-03-31 11:11:11
];

and it looks like this:

We aim to create a table where each MonthYear (e.g., Mar 2022) corresponds to one record (row) for every user who made payments during that MonthYear. Each record (row) will include the following columns:

• MonthYear (e.g., Mar 2022)
• user_id
• sum_amounts (the total sum of all payment amounts made by the user for the MonthYear)
• count_payments (the number of payments made by the user for the MonthYear)
  

For example, if user_id=3 made 100 payments in Mar 2022, and the amount of each payment was 2, then that table will contain one record for that user for Mar 2022:

Mar 2022

3

200

100

For the "payments" table listed above, the output would look like this:

MonthYear	user_id	sum_amounts	count_payments
Jan 2023	1	60	3
Jan 2023	2	15	1
Jan 2023	3	30	2
Feb 2023	1	60	3
Feb 2023	2	15	1
Mar 2023	2	15	1
Mar 2023	3	55	3

Do you have any suggestions on how we can achieve this? 🤔

[Ryan_McGregor · Accepted solution]

Hello there,

I was able to do that through creating a 'Resident' table that derives from your original "payments" table:

[payments]:

Load

ID, 

    USER_ID, 

    AMOUNT, 

    CREATED_AT,

    MonthName(CREATED_AT) as MonthNameCreated

Inline [

ID, USER_ID, AMOUNT, CREATED_AT

1, 1, 20, 2023-01-30 06:38:36

2, 2, 15, 2023-01-30 07:17:17

3, 1, 25, 2023-01-31 08:18:18

4, 3, 10, 2023-01-31 09:19:19

5, 3, 20, 2023-01-31 10:10:10

6, 1, 15, 2023-01-31 11:11:11

7, 1, 20, 2023-02-25 06:38:36

8, 2, 15, 2023-02-25 07:17:17

9, 1, 25, 2023-02-26 08:18:18

10, 1, 15, 2023-02-27 11:11:11

11, 2, 15, 2023-03-28 07:17:17

12, 3, 10, 2023-03-29 09:19:19

13, 3, 20, 2023-03-31 10:10:10

14, 3, 25, 2023-03-31 11:11:11

];

PaymentsMonthName:

Load

MonthNameCreated as MonthName,

    USER_ID as UserID,

    sum(AMOUNT) as SumAmounts,

    count(distinct ID) as CountPayments

Resident payments

Group By 

MonthNameCreated,

    USER_ID;

Store PaymentsMonthName Into 'zzz' (qvd);

Drop Table payments;

You would need to change the 'zzz' to wherever you store data. 

Hope this helps,

Ryan

[RoyBatty]

Thank you @Ryan_McGregor !