Display the list of inactive users in past 1 month

Q&A
, , ,
1

table1 :

user_id | website | request_datetime | operation
--------|---------|------------------|----------
1       | A       | 2023-06-01       | write
2       | B       | 2023-06-02       | read
3       | A       | 2023-06-03       | put
4       | B       | 2023-06-04       | write
7       | C       | 2023-04-05       | read
1       | D       | 2023-06-06       | put
2       | B       | 2023-06-07       | write
5       | A       | 2023-04-08       | read
3       | C       | 2023-06-09       | put
7       | C       | 2023-05-05       | put
6       | D       | 2023-04-11       | write
1       | A       | 2023-06-12       | read
4       | B       | 2023-06-13       | write
3       | C       | 2023-06-14       | put

Table2:

website | user_id
--------|--------
A       | 1, 3, 5
B       | 2, 4
C       | 3, 7
D       | 1, 6

I want a table visualization such that i get the user_id which have not visited their intended website in past 1 month. I am expecting the output to look like:

website	not_active_userid
A	                 5
B	
C	                 7
D	                 6

I know in terms of query of how to achieve this, but i dont know how to achieve this in quicksight

2

You can create a dataset by performing a left join between your 2 tables, with table 2 on the left side, and with user_id and website as your join clauses.

In your analysis create a calculated field for the request month and another to count the number of requests per month for each user.
request_month = truncDate(‘MM’, {request_datetime})
monthly_requests = countOver({request_datetime}, [{user_id}, {request_month}], PRE_AGG)

Finally add website and user_id to a table visual and filter it by last month and monthly_requests = 0.

3

Actually before creating your dataset, you’ll have to change your table2 so that you have one row for each user id.

website user_id
A 1
A 3
A 5
B 2
B 4
C 3
C 7
D 1
D 6
4

Thank you for the solution, it worked!!.
Unfortunately i found out about another caveat :
in table2 there could be website which is accessible to all the user_ids and is represented as ‘*’. Thus the table looks:

website | user_id
--------|---------
A       | 1
A       | 3
A       | 5
B       | 2
B       | 4
C       | 3
C       | 7
D       | 1
D       | 6
E       | *

and for example table1 will be like:

user_id | website | request_datetime | operation
------- | ------- | ---------------- | ---------
1       | A       | 2023-06-01       | write
2       | B       | 2023-06-02       | read
3       | A       | 2023-06-03       | put
4       | B       | 2023-06-04       | write
7       | C       | 2023-04-05       | read
1       | D       | 2023-06-06       | put
2       | B       | 2023-06-07       | write
5       | A       | 2023-04-08       | read
3       | C       | 2023-06-09       | put
7       | C       | 2023-05-05       | put
6       | D       | 2023-04-11       | write
1       | A       | 2023-06-12       | read
4       | B       | 2023-06-13       | write
3       | C       | 2023-06-14       | put
1       | E       | 2023-06-15       | put
2       | E       | 2023-06-16       | write

In the cases where all user_ids are accepted i wanted the joined table to have record as is, meaning i dont want null rows of other users who havent accessed. In the above case there are no records of website E being accessed by user_id 3,4,5,6,7, But i dont info regarding this.
My intended output after left join should be like :

website | user_id | request_datetime | operation
--------|---------|------------------|----------
A       | 1       | 2023-06-01       | write
A       | 3       | 2023-06-03       | put
A       | 5       |                  |
B       | 2       | 2023-06-02       | read
B       | 4       | 2023-06-04       | write
C       | 3       | 2023-06-09       | put
C       | 7       | 2023-04-05       | read
D       | 1       | 2023-06-06       | put
D       | 6       | 2023-04-11       | write
E       | 1       | 2023-06-11       | write
E       | 2       | 2023-04-11       | write

I have written a query for it, could you guide me on how to achieve this on quicksight:

SELECT
    a.website,
    CASE
        WHEN a.user_id = '*' THEN b.user_id
        ELSE a.user_id
    END AS user_id,
    b.request_datetime,
    b.operation
FROM
    tableA a
LEFT JOIN
    tableB b ON (a.user_id = b.user_id OR a.user_id = '*') AND a.website = b.website

Apologies for the trouble.

5

What’s your data source? If it’s a database, you can use the same query to create the dataset in Quick Sight. If it’s Excel, it gets a bit more complicated.