search

TogetherCrew / twitter-analytics

This repository is in responsible of twitter analytics tasks of TogetherCrew.
1 stars 0 forks source link

Queries for tests of twitter dashboard - "Audience Response" section #7

Open amindadgar opened 1 year ago

amindadgar commented 1 year ago

In this issue, we're going to write the queries to insert data related to the tests that would be implemented in backend. Basically, we have to write tests for the conditions below

Audience response

amindadgar commented 1 year ago

The data needed for the test Number of replies others made on the user's posts could be inserted using the queries below

MERGE (a:TwitterAccount {userId: "1111"})
MERGE (a2:TwitterAccount {userId: "1112"})
MERGE (t:Tweet {tweetId: "123", authorId: "1111"})
MERGE (t2:Tweet {tweetId: '124', authorId: "1111"})
MERGE (t3:Tweet {tweetId: '125', authorId: "1112"})
MERGE (t4:Tweet {tweetId: '126', authorId: "1111"})
MERGE (t5:Tweet {tweetId: '127', authorId: "1112"})

MERGE (a)-[:TWEETED {createdAt: '{Epoch2dayAgo}']->(t)
MERGE (a)-[:TWEETED {createdAt: '{Epoch1dayAgo}']->(t4)

MERGE (t2)-[:REPLIED {createdAt: '{Epoch1dayAgo}']->(t)
MERGE (t3)-[:REPLIED {createdAt: '{Epoch2dayAgo}']->(t)
MERGE (t5)-[:REPLIED {createdAt: '{Epoch4dayAgo}']->(t4)

The results for userId equal to 1111 should be equal to 2 and the results for userId equal to 1112 should be 0.

Note: The Epochs are timestamps in queries which are self explained as the variable name.

amindadgar commented 1 year ago

The data needed Number of retweets others made on the user's posts can be inserted using the query below

MERGE (a:TwitterAccount {userId: "1111"})
MERGE (a2:TwitterAccount {userId: "1112"})
MERGE (t:Tweet {tweetId: "123", authorId: "1111"})
MERGE (t2:Tweet {tweetId: '124', authorId: "1111"})
MERGE (t3:Tweet {tweetId: '125', authorId: "1112"})
MERGE (t4:Tweet {tweetId: '126', authorId: "1111"})
MERGE (t5:Tweet {tweetId: '127', authorId: "1112"})

MERGE (a)-[:TWEETED {createdAt: '{Epoch2dayAgo}']->(t)
MERGE (a)-[:TWEETED {createdAt: '{Epoch1dayAgo}']->(t4)

MERGE (t2)-[:RETWEETED {createdAt: '{Epoch1dayAgo}']->(t)
MERGE (t3)-[:REPLIED {createdAt: '{Epoch2dayAgo}']->(t)
MERGE (t5)-[:RETWEETED {createdAt: '{Epoch4dayAgo}']->(t4)

The results for userId equal to 1111 should be equal to 1 and the results for userId equal to 1112 should be 0.

amindadgar commented 1 year ago

The data needed for Number of likes others made on the user's posts can be inserted using the queries below

MERGE (a:TwitterAccount {userId: "1111"})
MERGE (a2:TwitterAccount {userId: "1112"})
MERGE (t:Tweet {tweetId: "123", authorId: "1111", createdAt: {Epoch5dayAgo}})
MERGE (t2:Tweet {tweetId: '124', authorId: "1111", createdAt: {Epoch4dayAgo}})
MERGE (t3:Tweet {tweetId: '125', authorId: "1112", createdAt: {Epoch7dayAgo}})
MERGE (t4:Tweet {tweetId: '126', authorId: "1111", createdAt: {Epoch3dayAgo}})
MERGE (t5:Tweet {tweetId: '127', authorId: "1112", createdAt: {Epoch8dayAgo}})

MERGE (a)-[:TWEETED {createdAt: {Epoch2dayAgo}]->(t)
MERGE (a)-[:TWEETED {createdAt: {Epoch3dayAgo}]->(t2)
MERGE (a)-[:TWEETED {createdAt: {Epoch5dayAgo}]->(t4)
MERGE (a2)-[:TWEETED {createdAt: {Epoch4dayAgo}]->(t3)
MERGE (a2)-[:TWEETED {createdAt: {Epoch3dayAgo}]->(t5)

MERGE (a)-[:LIKED {latestSavedAt: {Epoch1dayAgo}]->(t)
MERGE (a)-[:LIKED {latestSavedAt: {Epoch1dayAgo}]->(t2)
MERGE (a)-[:LIKED {latestSavedAt: {Epoch1dayAgo}]->(t3)
MERGE (a)-[:LIKED {latestSavedAt: {Epoch1dayAgo}]->(t5)

MERGE (a2)-[:LIKED {latestSavedAt: {Epoch1dayAgo}]->(t3)
MERGE (a2)-[:LIKED {latestSavedAt: {Epoch1dayAgo}]->(t5)

The results for userId equal to 1111 should be equal to 0 and the results for userId equal to 1112 should be 1.

amindadgar commented 1 year ago

For the test Number of Mentions we need to first insert the data into the database using the queries below

MERGE (a:TwitterAccount {userId: "1111"})
MERGE (a2:TwitterAccount {userId: "1112"})
MERGE (t:Tweet {tweetId: "123", authorId: "1111", createdAt: "{Epoch5dayAgo}"})
MERGE (t2:Tweet {tweetId: '124', authorId: "1111", createdAt: "{Epoch4dayAgo}"})
MERGE (t3:Tweet {tweetId: '125', authorId: "1112", createdAt: "{Epoch7dayAgo}"})
MERGE (t4:Tweet {tweetId: '126', authorId: "1111", createdAt: "{Epoch3dayAgo}"})
MERGE (t5:Tweet {tweetId: '127', authorId: "1112", createdAt: "{Epoch8dayAgo}"})

MERGE (a)-[:TWEETED {createdAt: '{Epoch2dayAgo}']->(t)
MERGE (a)-[:TWEETED {createdAt: '{Epoch3dayAgo}']->(t2)
MERGE (a)-[:TWEETED {createdAt: '{Epoch4dayAgo}']->(t4)
MERGE (a2)-[:TWEETED {createdAt: '{Epoch4dayAgo}']->(t3)
MERGE (a2)-[:TWEETED {createdAt: '{Epoch3dayAgo}']->(t5)

MERGE (t) -[:MENTIONED {createdAt: '{Epoch2dayAgo}'] -> (a)
MERGE (t) -[:MENTIONED {createdAt: '{Epoch2dayAgo}'] -> (a2)
MERGE (t2) -[:MENTIONED {createdAt: '{Epoch3dayAgo}'] -> (a2)
MERGE (t3) -[:MENTIONED {createdAt: '{Epoch4dayAgo}'] -> (a2)

The results for userId equal to 1111 should be equal to 0 and the results for userId equal to 1112 should be 2.