Assignment Details

All the table schemas used in this assignment are listed here:

clicks(display_id,ad_id,clicked)

primary key (display_id, ad_id)

events(display_id,uuid,document_id,timestamp,platform,geo_location)

primary key display_id

promoted_content(ad_id,document_id,campaign_id,advertiser_id)

primary key ad_id

documents_meta(document_id,source_id,publisher_id,publish_time)

primary key document_id

Questions:

Note: For the following questions, each question should be answered using one and only one query.

1. Retrieve all display events shown in tablet (platform). Please use events table and return all attributes of

the display event that satisfy the condition. (15 points)

In your solution, please provide the SQL query which can best answer the question. Also, please answer

how many rows are in the returned results of the provided query.

2. Retrieve all display events that is clicked and shown in desktop (platform). Please return all attributes of

the display event that satisfy the condition. (15 points)

In your solution, please provide the SQL query which can best answer the question. Also, please answer

how many rows are in the returned results of the provided query.

3. 3.1 Please use one SQL query to find out the following answers: per platform how many unique display

events have been clicked? per platform how many unique display events have not been clicked? Unique

events can be identified according to display_id. For query result, please return whether the events is

clicked or not, platform, the counts asked in the questions. If the count is 0, no need to include the tuple in

the query result. (10 points)

3.2 Please use one SQL query to find out the following answers: per platform how many unique ads

events have been clicked? per platform how many unique ads events have not been clicked? Unique

events can be identified according to ad_id. For query result, please return whether the events is clicked or

not, platform, the counts asked in the questions. If the count is 0, no need to include the tuple in the query

result. (5 points)

Hint: Each display_id has only one clicked ad. As long as the display_id has one ad being clicked,

then we say the display_id is clicked.

In your solution, please provide the SQL query which can best answer the question. Also, please provide

the screenshot of the query result.

4. Exam all advertiser's ads performance in different platforms. We use click through rate to indicate ads

performance. Click through rate is defined as the number of ad events have been clicked divided by the

number of all ad events. SQL query needs to return ad_id, platform, click through rate. In the SQL query

result, the ad and platform with highest click through rate needs to be shown first, and then show the ad

and platform with the second highest click through rate, so on so forth, until the the ad and platform with

lowest click through rate. Also in the result, please show click through rate as "ctr". (20 points)

In your solution, please provide the SQL query which can best answer the question. Also, please answer

how many rows are in the returned results of the provided query. Also, please provide the screenshot

of the first 5 rows in the returned results.

5. Exam each advertiser's ads performance across different publishers. For each advertiser, we want to see

how their ads are performed in different publishers. Ads performance is measured by click through rate. It

is defined in the Question 4. publisher_id can be found in documents_meta table. Please use

documents_meta table in your query. In the SQL query result, Please return ad_id, publisher_id, show click

through rate as "ctr". (20 points)

In your solution, please provide the SQL query which can best answer the question. Also, please answer

how many rows are in the returned results of the provided query.

6. Retrieve all events displayed in desktop (platform) and in US. Please use geo_location field to decide

whether a display is shown in US or not. In the SQL query result, please return all attributes of the display

event that satisfy the condition. (15 points)

In your solution, please provide the SQL query which can best answer the question. Also, please answer

how many rows are in the returned results of the provided query.

Hint:

1. For each question, for answering the question about how many rows are in the returned results, you don't

need to count the rows manually, you can use one additional query to do that. For example, if the original

query is Select ID from demo where Name = "Twitter" , you can write a new query like this to count

the number of rows being returned in the first query select count(*) from (select ID from demo

where Name like "Twitter") a . Basically, the idea is to include the first query as a subquery in the

second query to count number of tuples returned in the first query