Trendige Suchbegriffe ohne Admin/Moderator-Suchen?

Daten & Berichterstattung
2

Hallo @Kieran_Berryman :wave:

Hmmm, vielleicht probieren Sie das mal? :thinking:

--[params]
-- date :start_date
-- date :end_date

WITH admin_moderator_users AS (
    SELECT 
        gu.user_id
    FROM 
        group_users gu
    JOIN 
        groups g ON gu.group_id = g.id
    WHERE 
        g.name IN ('admins', 'moderators')
)

SELECT 
    lower(sl.term) AS search_term,
    count(sl.id) AS term_count,
    (COALESCE(SUM(CASE WHEN sl.search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(sl.id)::float) * 100 AS click_through_rate,
    SUM(CASE WHEN sl.search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM 
    search_logs sl
LEFT JOIN 
    admin_moderator_users amu ON sl.user_id = amu.user_id
WHERE 
    sl.created_at::date BETWEEN :start_date AND :end_date
    AND amu.user_id IS NULL
GROUP BY 
    lower(sl.term)
ORDER BY 
    term_count DESC

Hier ist die SQL für den Dashboard-Bericht „Trending Search Terms“:

2 „Gefällt mir“