SQL Casting without the use of ::

Question

Question: Calculate the percentage of search results, out of all the results, that were positioned in the top 3 and clicked by the user.

We have two tables that contain search results. The 'fb_search_results' table contains the search results from a user's search. In this table, search_id is a key that corresponds to the search_id column of the fb_search_events table. The position column refers to the position of the result. The 'fb_search_events' is a table that stores whether or not the user clicked on a particular search result.

Solution:

SELECT
    COUNT(CASE WHEN search_results_position <=3 AND clicked = 1 THEN b.search_id ELSE NULL END)::FLOAT/COUNT(*))*100 as percentage
FROM fb_search_results a
LEFT JOIN fb_search_events b
    ON a.search_id = b.search_id

My attempt:

WITH new_view AS(
    SELECT
        COUNT(CASE WHEN search_results_position <=3 AND clicked = 1 THEN b.search_id ELSE NULL END)
    FROM fb_search_results a
    LEFT JOIN fb_search_events b
        ON a.search_id = b.search_id)
SELECT CAST(FLOAT/COUNT(*)*100 FROM new_view)) AS percentage

How do I go about adding everything after "::" from the solution to my own attempt? I get a syntax error at or near "FROM". Thank you

Answer 1

For transform you original query to a CTE, use a column aliases:

with new_view as (
  select
    sum(case when search_results_position <=3 and clicked = 1 then 1 end) as top_clicks,
    count(*) as clicks
  from fb_search_results a
  left join fb_search_events b on a.search_id = b.search_id
)
select
  100 * top_hit_clicks::float/clicks as percentage
from new_view