관리자/모더이터 검색 제외된 인기 검색어?

[Lilly (Lillian)]

안녕하세요 @Kieran_Berryman

hmmm 이걸 한번 시도해 볼까요?

--[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

---

다음은 트렌딩 검색어 대시보드 리포트용 SQL입니다:

대시보드 보고서 - 트렌딩 검색어

This is an SQL version of the Dashboard Report for Trending Search Terms. This report provides insight into the search terms used on a site within a specified date range, and helps understand which terms are most frequently searched for and how often users click on the search results. --[params] -- date :start_date -- date :end_date SELECT lower(term) AS search_term, count(id) AS term_count, (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 10…