2. DATA MANIPULATION Suppose you are given 2 tables pertaining to Bing.com data:

Question

2. DATA MANIPULATION Suppose you are given 2 tables pertaining to Bing.com data: a. Name: Impressions lic:led: Impression_Id:string, Query:string, Request_Time:DateTime Description: Each line represents a unique impression on Bing.com. The impression id is the primary key for the table, the query string is the query that the user put in the search box, and request time is the time the request from the user was issued b. Name: Clicks lid: Impression_Id:string, Click_ld:string:string, Click_Time:string Description: Each line represents a unique click on Bing.com. The impression id is mapped from the impression table, the click id is the primary key and the click time is the time the user clicked. We define click through rate for a query as the following ratio: the number of clicks on impressions for that query/the number of impressions for the query Write SQL query to find the queries with the top 20 highest click through rate.

Explanation / Answer

If you have any doubts, please give me comment...

SELECT Query, no_of_clicks/SUM(no_of_clicks) AS clicks_through_rate

FROM (

SELECT Query, COUNT(*) AS no_of_clicks

FROM Impressions I, Clicks C

WHERE I.Impression_Id = C.Impression_Id

GROUP BY Query

) AS T

ORDER BY clicks_through_rate DESC

LIMIT 20;

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.