- ******************************************************************************************************
**********************************************************************- ************************************************
Table: search_result
col_name | col_type -------------+-------------------------- date | date search_id | bigint result_id | bigint result_type | varchar(20) action | varchar(20)
Sample results
ctr ---------- 2.34
Solution postgres
SELECT
COUNT(DISTINCT CASE WHEN action = 'click' THEN search_id ELSE NULL END) * 100.0/COUNT(DISTINCT search_id)
FROM search_result
WHERE date = '2021-01-01';
Explanation
This query is selecting data from a table called "search_result" where the date is equal to January 1st, 2021. It is then calculating the percentage of searches that resulted in a click.
The first part of the query is using a conditional statement to count only the search IDs where the action was a click. It does this by using a CASE statement to evaluate whether the action was a click or not. If it was a click, it returns the search ID. Otherwise, it returns NULL.
The second part of the query is counting the number of distinct search IDs in the table.
Finally, the query divides the count of clicks by the count of distinct search IDs and multiplies by 100 to get the percentage of searches that resulted in a click.
This query is useful for analyzing the effectiveness of a search engine or website in terms of user behavior, as it provides insight into how often users are clicking on search results.
Expected results