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
andSESSION_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?
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.
Don’t hesitate to comment your code on why you chose a solution over another