Je pense que quelque chose comme ceci serait plus approprié :
-- [params]
-- date :start_date
-- date :end_date
WITH results AS (
SELECT LOWER(term) AS term,
COUNT(*) AS searches,
SUM(CASE WHEN search_result_id IS NOT NULL THEN 1 ELSE 0 END) AS click_through
FROM search_logs
WHERE created_at BETWEEN :start_date AND :end_date
GROUP BY term
)
SELECT term AS "Terme de recherche",
searches AS "Recherches",
click_through AS "Clics",
CEIL ((click_through/searches::float) * 100) || '%' AS "CTR"
FROM results
ORDER BY searches DESC
J’espère que cela vous aidera, mais faites-moi savoir si vous souhaitez des ajustements. 