Cafeyn Technical Test

Numan SAHNOU

1. SQL

issue_consumption:

ID_USER, ID_ISSUE, ID_MAGAZINE

ID_ISSUE is a serial number identifier of a magazine, i.e: the magazine 88134 has 23 issues ranging from 1 to 23… the magazine 652273 has 100 issues ranging from 1 to 100 etc…)
ID_MAGAZINE is a unique identifier

article_consumption:

ID_USER, ID_ISSUE, ID_ARTICLE, ID_MAGAZINE

ID_ARTICLE is a unique identifier

user_session:

ID_USER, SESSION_LOGIN, SESSION_LOGOUT

SESSION_LOGIN and SESSION_LOGOUT are timestamps in the “yyyy-mm-DD hh:mm:ss” format *ID_USER is a unique integer identifier that can take the value -1 to represent anonymous users(users that are not logged in to their account)

After studying these tables write the sql queries that would best answer these questions:

Describe in a few words what you have understood about these tables

These 3 tables represent the informations related to the user's session and his consumption of the content such as articles and magazines. The "issue_consumption" table references all the users who consumed a magazine (an issue of a magazine). "article_consumption" is about the users who consumed an article from a magazine. Finally the "user_session" references all the sessions from different users

On average, how much time does a user session last?

SELECT AVG(DATEDIFF(minute, SESSION_LOGIN, SESSION_LOGOUT)) AS 'avg_session' FROM user_session; #This query returns the average session duration between the login and the logout of all the users in minutes. We can also specify by second, hour, day, week etc

Calculate The duration of longest session ever

SELECT MAX(DATEDIFF(minute, SESSION_LOGIN, SESSION_LOGOUT)) AS 'longest_session' FROM user_session; #This query returns the longest session duration (minutes)

Extract the list of unique non anonymous users that have read an issue but have never read any articles

SELECT DISTINCT i.ID_USER AS 'non_anonymous_issue_no_article' FROM issue_consumption i LEFT JOIN article_consumption a ON i.ID_MAGAZINE = a.ID_MAGAZINE WHERE (i.ID_USER NOT NULL) AND (a.ID_MAGAZINE IS NULL); #I use LEFT JOIN because I want to list all results from the issue_comsumption table even if there is no match in the article_consumption table #and I simply select all the distinct ID_USER that exists and where the ID_MAGAZINE is NULL

In total, how many issues have been read by anonymous users and logged users respectively

SELECT COUNT(CASE WHEN ID_USER IS NULL THEN 1 END) AS 'nb_issues_from_anonymous', COUNT(CASE WHEN ID_USER NOT NULL THEN 1 END) AS 'nb_issues_from_logged' FROM issue_consumption; #In the first case I count the number of anonymous users present in the issue_consumption table (just add +1 to the counter when the case condition is satisfied) #same when the user exists

The maximum number of articles read by a user, the maximum number of times a user has read the same article

SELECT MAX(SELECT COUNT(ID_ARTICLE) FROM article_consumption GROUP BY ID_USER) AS 'nb_articles_read_by_user'; SELECT MAX(SELECT COUNT(*) FROM article_consumption GROUP BY ID_ARTICLE, ID_USER) AS 'nb_times_article_read_by_user';

Bonus

Which is the least read magazine, and which is the magazine that has the least read number of articles

SELECT ID_MAGAZINE AS 'least_read_magazine' FROM issue_consumption GROUP BY ID_MAGAZINE ORDER BY COUNT(ID_MAGAZINE) limit 1; SELECT i.ID_MAGAZINE AS 'least_read_article_magazine' FROM issue_consumption i INNER JOIN article_consumption a ON i.ID_MAGAZINE = a.ID_MAGAZINE GROUP BY a.ID_ARTICLE ORDER BY COUNT (a.ID_ARTICLE) limit 1;

2. Python

Given a parameter k, an integer > 0, and a list L of integers > 0 (like 3,8,5,3,7,1,2,9 for instance), write a function that given k and L, will output all the distinct combinations of two numbers of the list which sum equals k (not especially ordered)

For instance if k = 8 and L = 1,4,9,6,5,3,7,2, the function should have (3,5), (1,7), (2,6) as output.

Don’t hesitate to comment your code on why you chose a solution over another

def cafeyn_function(k, L): # Thanks to set(), we won't have duplicates elements so we have all the distinct complements elements of L which in the complement set. # That way, we just have to create the pair of each element (x, k-x) because x+(k-x) = k # Moreover, using this method allows us to perform a single for loop complement = set() #the pair set will contain all the distinct complements elements (k-x) for each x in L result = set() #the result set will contain all the distinct pairs (x, k-x) for x in L: # Here we are looking if the element x is in the complement set, if so it means that we just have to append the pair (x and k-x) to the final result if x in complement: result.add((x, k-x)) # If the element x is not found in the complement set, we simply add the complement of x which is (k-x) in the pair set else: complement.add(k-x) return result print(cafeyn_function(8, [1,4,9,6,5,3,7,2])) print(cafeyn_function(5, [3,8,5,3,7,1,2,9]))

Cafeyn Technical Test

Numan SAHNOU

1. SQL

issue_consumption:

article_consumption:

user_session:

After studying these tables write the sql queries that would best answer these questions:

Describe in a few words what you have understood about these tables

On average, how much time does a user session last?

Calculate The duration of longest session ever

Extract the list of unique non anonymous users that have read an issue but have never read any articles

In total, how many issues have been read by anonymous users and logged users respectively

The maximum number of articles read by a user, the maximum number of times a user has read the same article

Bonus

Which is the least read magazine, and which is the magazine that has the least read number of articles

2. Python

Given a parameter k, an integer > 0, and a list L of integers > 0 (like 3,8,5,3,7,1,2,9 for instance), write a function that given k and L, will output all the distinct combinations of two numbers of the list which sum equals k (not especially ordered)

For instance if k = 8 and L = 1,4,9,6,5,3,7,2, the function should have (3,5), (1,7), (2,6) as output.

Recommended on Deepnote

Stock Market Analysis

The 10 Best Ways to Create NumPy Arrays

Wide Residual Networks