gistfile1.txt

select *
from (
         select query_expansion.keyword                                                               as original_query
              , search_sessions.query                                                                 as guided_query
              , query_expansion.rank                                                                  as guide_rank
              , users.name                                                                            as author_name
              , recipes.id                                                                            as recipe_id
              , recipes.title                                                                         as recipe_title
              , recipes.published_at                                                                  as published_at
              , count(distinct comments.user_id)                                                      as cooksnappers
              , count(distinct cookplan_shortlists.user_id)                                           as cookplanners
              , rank() over (partition by guided_query order by cookplanners desc, cooksnappers desc) as rank

         from global_dwh.search_sessions
                  inner join global_mst.recipes on search_sessions.recipe_id = recipes.id
                  inner join global_mst.users on recipes.user_id = users.id
                  inner join global_mst.comments on recipes.id = comments.recipe_id
                  inner join global_mst.cookplan_shortlists on recipes.id = cookplan_shortlists.recipe_id
                  inner join (select keyword,
                                     suggestion,
                                     rank() over (partition by keyword order by score desc) as rank
                              from global_search.guides
                              where activated_at is not null
                                and keyword in (
                                                'ayam', 'udang', 'cumi', 'tahu', 'bolu kukus', 'tempe', 'ayam goreng',
                                                'daging', 'telur',
                                                'sayur',
                                                'buncis', 'ikan', 'roti tawar', 'soto', 'jamur'
                                  )
         ) query_expansion on search_sessions.query = query_expansion.suggestion

         where comments.attachments_count > 0
           and comments.created_at > '2020-01-01'::timestamp
           and cookplan_shortlists.created_at > '2020-01-01'::timestamp
           and recipes.published_at > '2019-12-01'::timestamp
           and search_sessions.event_time > '2020-05-01'::timestamp
           and search_sessions.language = 'id'

         group by 1, 2, 3, 4, 5, 6, 7
     ) t
where t.rank < 5
  and t.cooksnappers > 0
;