Metrics: "Account activity" section

[amindadgar]

This issue is created to share the cypher queries for account activity section to be implemented in backend. We have two widgets in this section

• [x] Accounts that engage with you
• [x] Your followers

[amindadgar]

The query for the accounts count that engages with you

OPTIONAL MATCH (t:Tweet {authorId: '{userId}'})<-[r:QUOTED|REPLIED|RETWEETED]-(m:Tweet)
WHERE m.authorId <> t.authorId AND r.createdAt >= {Epoch7daysAgo}
WITH COLLECT(DISTINCT m.authorId) as interaction_authors

OPTIONAL MATCH (a:TwitterAccount {userId: '{userId}'}) <-[r:MENTIONED]-(t:Tweet)
WHERE t.authorId <> a.userId AND r.createdAt >= {Epoch7daysAgo}
WITH COLLECT(DISTINCT t.authorId) as mention_authors, interaction_authors

OPTIONAL MATCH (t:Tweet {authorId: '{userId}'}) <-[r:LIKED]-(a:TwitterAccount)
WHERE t.authorId <> a.userId AND t.createdAt >= {Epoch7daysAgo}
WITH COLLECT(DISTINCT a.userId) as liked_authors, mention_authors, interaction_authors

WITH liked_authors + interaction_authors + mention_authors as people_list
UNWIND people_list as people
RETURN COUNT( DISTINCT people) as account_count

Note: This query will return an integer number of the accounts that engaged with you in past 7 days. Note 2: Please replace {userId} with the main twitter account id. Note 3: Please replace {Epoch7daysAgo} with 7 days ago timestamp. for example a timestamp for the date datetime.datetime(2023, 9, 15, 12, 49, 30, tzinfo=datetime.timezone.utc) would be 1694782170000

[amindadgar]

your follower count

MATCH (a:TwitterAccount {userId: '{userId}'})
RETURN a.followerCount as followerCount

[scientiststwin]

@amindadgar Have you written test data for the above queries? if yes, please refer to the related issue

[amindadgar]

@amindadgar Have you written test data for the above queries? if yes, please refer to the related issue

Thanks for asking. Here's the test cases you're looking for https://github.com/TogetherCrew/twitter-analytics/issues/12