Daily CTR by Category | Dataford Interview Questions

Given a table of customer search queries and clicked results, write a query to calculate the click-through rate (CTR) for each category on a daily basis on Thumbtack.

Use the provided search and click tables. Count each search query once, even when it has multiple clicked results, and exclude rows with a missing category.

Output

One row per date and category, with search_date, category, searches, clicked_searches, and ctr_percent.
Include categories with no clicks, order by search_date ascending and category ascending.

Schema

search_queries

Column	Type	Description
query_idPK	INT	Unique identifier for a customer search query
searched_at	TIMESTAMP	Timestamp when the search was submitted
category	VARCHAR(100)	Thumbtack service category associated with the search

clicked_results

Column	Type	Description
click_idPK	INT	Unique identifier for a clicked result event
query_id	INT	Search query that produced the clicked result
clicked_at	TIMESTAMP	Timestamp when the result was clicked

Your question is Daily CTR by Category. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem